Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tabular using DAX: Possible to lookup across multiple tables (LOOKUPVALUE or CALCULATE?) Expand / Collapse
Author
Message
Posted Friday, June 13, 2014 2:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:13 PM
Points: 286, Visits: 1,160
I am working with some row filters and am having a heck of a time trying to lookup across multiple tables.



I need to return States[StateID] row in States where an Employee has a related CityID.

So States[StateID] to Cities[StateID] on Citites[CityID] to Employees[CityID] perhaps where Employees[Employee ID] is 3 or Employees[Login ID] = "Sam".

I can not seem to get this to work.

I can easily do Cities to Employees on Employee Login ID that isn't an issue:
LOOKUPVALUE(Employees[CityID],Employees[Login Id],"Sam",Employees[CityID],Cities[CityID])

it is Starting at State and moving through Cities to Employees that I just can't seem to do....
Post #1580728
Posted Monday, August 11, 2014 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:03 AM
Points: 1, Visits: 19
Can you be more specific about what you are trying to do here? If you were to add a pivot table to excel and took the Employee Name as a row value, then added the State as a slicer, when you click on a state, it would only show you the employees for that state. That is because of the filter context that would be passed in from Excel.
Post #1602008
Posted Monday, August 25, 2014 6:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 5, 2014 1:15 AM
Points: 9, Visits: 55
You could pull through the StateID or StateName as a calculated column into the Employee table using the formula below. Then you should be able to filter as you need.

=RELATED(State[StateID])

Though I agree with the previous comment, that you havent provided enough context of the issue.

Thanks
Post #1607303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse