Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Lookup using DAX

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

 

Need Help DAX
  • smpa01's avatar
    smpa01
    4 years ago

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

     

     

     

     

     

     

     

4 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

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

     

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks! @smpa01 your solution worked for me.

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

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