Forum Discussion

elliotdixon's avatar
elliotdixon
Responsive Resident
10 years ago
Solved

Lookup two columns - Related()?

Hi All, I think I should be using the related function here but am not sure. Just want to do a lookup on another table   I have a locations table Location_ID Location Name 1 Mars...
Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    You can try this 

    =
LOOKUPVALUE (
    locations[Location Name];
    locations[Location_ID]; routes[Location_from]
)
    & " to "
    & LOOKUPVALUE (
        locations[Location Name];
        locations[Location_ID]; routes[Location_to]
    )
PowerBIGuy's avatar
PowerBIGuy
Responsive Resident
10 years ago

elliotdixon Since there's no concept of a role playing dimension(meaning a dimension that can be joined to a fact table more than once) You may have to create a copy of your Locations table maybe try making one "from" location and the other "to" location. You can still use the same code.

 

 

 

 

Full Route = 
LOOKUPVALUE (From_Locations[LOCATION_NAME],From_Locations[LOCATION_ID], Routes[ROUTE_LOCATION_FROM_ID])
 & " to "
& LOOKUPVALUE (To_Locations[LOCATION_NAME],To_Locations[LOCATION_ID],
Routes[ROUTE_LOCATION_TO_ID])

 

elliotdixon's avatar
elliotdixon
Responsive Resident
10 years ago

Cheers PowerBIGuy - actually missed your reply - I did what you recommended though. Cheers.