Forum Discussion
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 |
2 | Venus |
3 | Saturn |
4 | Jupiter |
I then have other tables where routes are created.
Location_from | Location_to |
1 | 3 |
4 | 1 |
1 | 2 |
I need to create the actual route with words though so in the routes table I can have.
I realise I can use the concatenate function to join the names but just need to know how to look up the Location Name from the locations table.
Location_from | Location_to | Full_route_name |
1 | 3 | Mars to Saturn |
4 | 1 | Jupiter to Mars |
1 | 2 | Mars to Venus |
The related function does not work as it returns only the first location. As my routes have two locations I need to look up both. Not sure how to do this.
Thanks in advance.
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] )
8 Replies
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]))
Forgive me for distracting, but how are you plan on visualizing this data?
I ask because I have been wanting to be able to show 'Routes' or 'Lanes' between countries but have been unable to do it seeing as I cannot plot 'Origin' & ' Destination' locations with the map.
Was curious how you were visualizing
hi cwayne758 I am just going to visualise this with a table. For some logging trucks going from forrests to ports and I just need a count of the trips. Not really sure how you could get that 'route' data onto a map. Will be interesting if possible though.
ED
