Post reply

Tabular using DAX: Possible to lookup across multiple tables (LOOKUPVALUE or CALCULATE?)

  • June 13, 2014 at 2:07 pm

    #146885

    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....

  • August 11, 2014 at 12:06 pm

    #1736986

    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.

  • August 25, 2014 at 6:37 pm

    #1740888

    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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply