merge 3 tables in one table in dax

[Wadda7AboUdai]

Hello everyone

I want to convert this code from sql to Dax

(SELECT T1.DIMENSIONATTRIBUTE, T1.ENTITYINSTANCE, T1.PARTITION
, T2.DISPLAYVALUE, T3.DIMENSIONATTRIBUTEVALUECOMBINATION AS VALUECOMBINATIONRECID
FROM DIMENSIONATTRIBUTEVALUE AS T1 INNER JOIN
DIMENSIONATTRIBUTELEVELVALUE AS T2 ON T1.RECID = T2.DIMENSIONATTRIBUTEVALUE AND T1.PARTITION = T2.PARTITION INNER JOIN
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION AS T3 ON T2.DIMENSIONATTRIBUTEVALUEGROUP = T3.DIMENSIONATTRIBUTEVALUEGROUP AND T2.PARTITION = T3.PARTITION
GROUP BY T1.DIMENSIONATTRIBUTE, T1.ENTITYINSTANCE, T1.PARTITION, T2.DISPLAYVALUE, T3.DIMENSIONATTRIBUTEVALUECOMBINATION

[Anonymous · Accepted solution]

Hi @Wadda7AboUdai ,

First use NATURALINNERJOIN or FILTER and RELATED to create a new table that represents the join between T1 and T2.

Step1_Result = 
NATURALINNERJOIN(
    DIMENSIONATTRIBUTEVALUE,
    DIMENSIONATTRIBUTELEVELVALUE
)

Then join the result with the third table T3.

Final_Result = 
NATURALINNERJOIN(
    Step1_Result,
    DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
)

Finally, use SELECTCOLUMNS to select the desired columns from the result of Step 2.

Merged_Table = 
SELECTCOLUMNS(
    Final_Result,
    "DIMENSIONATTRIBUTE", DIMENSIONATTRIBUTEVALUE[DIMENSIONATTRIBUTE],
    "ENTITYINSTANCE", DIMENSIONATTRIBUTEVALUE[ENTITYINSTANCE],
    "PARTITION", DIMENSIONATTRIBUTEVALUE[PARTITION],
    "DISPLAYVALUE", DIMENSIONATTRIBUTELEVELVALUE[DISPLAYVALUE],
    "VALUECOMBINATIONRECID", DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION[DIMENSIONATTRIBUTEVALUECOMBINATION]
)

But please note that this is just directly written by me based on the SQL you provided, if it's not correct, please also provide me with sample data from your three data tables, thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.