topic Re: Correct measure to use to mapping in Bridge table in DAX Commands and Tips

Correct measure to use to mapping in Bridge table

InsHunter — Sat, 26 Aug 2023 04:25:27 GMT

Hi,

Being a new bie to PBI Need help to arrive at correct measure to take in to account the bridging table .
Recreated my actual case with simple sample data.
Two fact tables,Three dim tables and a Bridge table.
Bridge table has n to 1 relationship as well as 1 to n relation ship between ID1 and ID2

My current measure does not take in to account the relation ship while calculation as it still calculates the values for independent IDs and not taking the ID mapping to account. Expected correct values given in red after aggregation.

https://drive.google.com/file/d/1NwijYKBDzng82mm6jXpthB8SkWAG1OJo/view?usp=drive_link

Enclosing the PBIX sample file.
Deeply appreciate your support.

Thanks.

Re: Correct measure to use to mapping in Bridge table

lbendlin — Sun, 27 Aug 2023 22:42:49 GMT

Are you familiar with the TREATAS function?

Re: Correct measure to use to mapping in Bridge table

InsHunter — Mon, 28 Aug 2023 04:00:51 GMT

lbendlin -Sorry i need to explore. If you can suggest that measure,will be of great help. Thank u for your response

Re: Correct measure to use to mapping in Bridge table

InsHunter — Mon, 28 Aug 2023 16:48:51 GMT

lbendlin
Request to confirm whether the below approach is right in current context

Energy_per_cum(Rev1) = CALCULATE( VAR Units = CALCULATE( [Energy (Units)], TREATAS( SUMMARIZE( Bridge_Table,Bridge_Table[ID1]), Fact_Table1[ID1]) ) VAR Flow_cum = CALCULATE( [Flow (Ltrs)], TREATAS( SUMMARIZE( Bridge_Table,Bridge_Table[ID2]), Fact_Table2[ID2] ) ) VAR Units_per_Cum = DIVIDE(Units,Flow_cum) RETURN Units_per_Cum )

Re: Correct measure to use to mapping in Bridge table

lbendlin — Mon, 28 Aug 2023 18:34:06 GMT

Yeah, sort of. The good thing about TREATAS is that it works (better) without joins.

Re: Correct measure to use to mapping in Bridge table

InsHunter — Tue, 29 Aug 2023 00:40:47 GMT

lbendlin -Thanks for your suggestion