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]
    )
elliotdixon's avatar
elliotdixon
Responsive Resident
10 years ago

Wow konstantinos that looks perfect and definitly looks like the forumula I need.

 

It only half works though. Shows the Location from and the "to" but not the location to details.

I believe this is due to the fact that the relationship between the two tables exists as

Routes[ROUTE_LOCATION_FROM_ID] -> Locations[LOCATION_ID]

Cardinality = Many to One

Cross filter direction = Both

 

If I change the connection to be from

Routes[ROUTE_LOCATION_TO_ID] -> Locations[LOCATION_ID]

it then shows nothing for location from but does show "to" and the Location to.

 

Any idea what I need to do to the relationship to get this working?

 

Cheers

 

Code I am using is

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

 

 

 

Cardinality

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.