Lookup using DAX

[sarthaks]

Hi All,

I have two tables: Cargo and Location Detials (Its a lookup/master table) and below are some sample data

Cargo Table:



CargoId	 Origin 	 Destination
1	   01	           04
2	   02	           05
3	   03	           06


Location Details



LocationID	  LocationName
    01	             LOC01
    02	             LOC02
    03	             LOC03
    04	             LOC04
    05	             LOC05
    06	             LOC06

Requirement is to replace the Origin and Destination columns in Cargo table with actual location name from the Location Details master table.

Please let me know how to achieve using DAX, i tried to create relationship and use LookUP but its not working as we cant maintain relationship with lookup table twice (one using Origin and LocationName, other using Destination and LocationName)

Please suggest any approach.

Thanks in advance

[smpa01 · Accepted solution]

@sarthaks  you can use the following measures which works with or without any relationship

_origin =
CALCULATE (
    MAX ( Location[locationName] ),
    TREATAS ( VALUES ( Cargo[origin] ), Location[locationID] )
)

_destination =
CALCULATE (
    MAX ( Location[locationName] ),
    TREATAS ( VALUES ( Cargo[destination] ), Location[locationID] )
)

[sarthaks]

Thanks! @smpa01 your solution worked for me.

[CNENFRNL]

To my understanding, LOOKUPVALUE is a legacy from Excel. Here's use a more PBI way to deal with it, which involves inactive relationship and expanded table,

Name Org = CALCULATE(MAX(LOC[LocationName]), CARGO)

Name Dest =
CALCULATE(
    MAX( LOC[LocationName] ),
    CALCULATETABLE(
        CARGO,
        USERELATIONSHIP ( CARGO[Destination], LOC[LocationID] )
    )
)

[Jos_Woolley]

Hi,

Not sure what you mean when you say "Requirement is to replace the Origin and Destination columns...". You can't overwrite data or remove columns with DAX.

You can add two additional columns to your Cargo table, e.g.:

Origin Location =
LOOKUPVALUE ( Location[LocationName], Location[LocationID], Cargo[Origin] )

A similar construction could be used to derive the Destination Location.

Regards