Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] )
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |