Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Table1 and table2 has many-many relationship so I solved that relationship by creating bridge table.
So I have 3 tables: table1, table2, bridge table
Table1-bridge has Many to 1 relationship
Table2-bridge has Many to 1 relationship
Table1 has column and values as:
Item no Dealer OnHandQty
1 D1 5
1 D2 10
1 D3 3
2 D1 2
2 D4 4
2 D3 5
3 D1 5
4 D3 4
Table2 has column and values as:
Item no Dealer
1 D1
1 D2
2 D1
2 D4
3 D1
4 D3
Bridge tabel has column and values as:
Item no Description
1 Cycle
2 ABC
3 XYZ
4 Pen
I want DAX to calculate sum of OnHandQty from table1 based on dealername in table2, result table such as:
i.e. For ItemNo 1 - (D1+D2 OnHandQty = 15) or so...
Item no Qty
1 15
2 6
3 5
4 4
Please help me in making this kind of DAX.
Solved! Go to Solution.
@Anonymous
Try this MEASURE
Measure = CALCULATE ( SUM ( Table1[OnHandQty] ), INTERSECT ( VALUES ( Table1[Dealer] ), VALUES ( Table2[Dealer] ) ) )
or this one
Measure 2 = CALCULATE ( SUM ( Table1[OnHandQty] ), TREATAS ( VALUES ( Table2[Dealer] ), Table1[Dealer] ) )
@Anonymous
Try this MEASURE
Measure = CALCULATE ( SUM ( Table1[OnHandQty] ), INTERSECT ( VALUES ( Table1[Dealer] ), VALUES ( Table2[Dealer] ) ) )
or this one
Measure 2 = CALCULATE ( SUM ( Table1[OnHandQty] ), TREATAS ( VALUES ( Table2[Dealer] ), Table1[Dealer] ) )
@Zubair_Muhammad Thanku so much!! It works!! Can you please explain this Treatas DAX littlebit.
@Anonymous
Following is a very useful article to understand TREATAS
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
Crux is that TREATAS can be used to filter a column/Table using values of an unrelated/indirectly related table
INTERSECT also does a very similar job
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |