topic Lookup using DAX in DAX Commands and Tips

Lookup using DAX

Anonymous — Sun, 24 Oct 2021 02:33:01 GMT

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

Re: Lookup using DAX

Jos_Woolley — Sun, 24 Oct 2021 05:36:13 GMT

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

Re: Lookup using DAX

CNENFRNL — Sun, 24 Oct 2021 06:20:51 GMT

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

Re: Lookup using DAX

smpa01 — Mon, 25 Oct 2021 13:48:37 GMT

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

Re: Lookup using DAX

Anonymous — Thu, 28 Oct 2021 13:46:43 GMT

Thanks! @smpa01 your solution worked for me.