March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
@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 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] )
)
Thanks! @smpa01 your solution worked for me.
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] )
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
22 | |
20 | |
18 |