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

konstantinos's avatar
konstantinos
Memorable Member
10 years ago

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