Forum Discussion
Lookup two columns - Related()?
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] )
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]
)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])
Hi elliotdixon You don't need a relantionship, you can delete it unless you need it for other visuals and calculations.
First I suppose you don't need "both" directions since sometimes gives results that are unexpected unless you need it for data modelling issues. Most cases you need "one" direction.
I haven't test it but if you don't want to delete the relantionship you can try this ( RELATED for the active relantionship )
// No relationships 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]) // One active relationship Full Route 2= RELATED([ROUTE_LOCATION_FROM_ID]) & " to " & LOOKUPVALUE (Locations[LOCATION_NAME],Locations[LOCATION_ID], Routes[ROUTE_LOCATION_TO_ID]) // One active relationship & one inactive relationship Full Route 3= RELATED([ROUTE_LOCATION_FROM_ID]) & " to " & CALCULATE (RELATED([ROUTE_LOCATION_TO_ID]);
USERELANTIONSHIP(Locations[LOCATION_ID;[ROUTE_LOCATION_TO_ID]))Thanks konstantinos good to see a possible solution going down that Related() path.
Your recommendation prompted me to have a further hunt around and I found a good post from Andreas De Ruiter
As I need the relationship for other calculations I have decided that simply adding another table for the locations is easiest. Thanks so much for your help with this.
ED
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])
Cheers PowerBIGuy - actually missed your reply - I did what you recommended though. Cheers.
