Lookup two columns - Related()?

[elliotdixon]

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.

[cwayne758]

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 

[elliotdixon]

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

[konstantinos · Accepted 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]
    )

[elliotdixon]

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])

[konstantinos]

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]))

[elliotdixon]

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

http://blogs.msdn.com/b/andreasderuiter/archive/2012/12/12/solving-you-cannot-activate-the-relations...

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

[PowerBIGuy]

@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]

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