Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
89 | |
87 | |
50 | |
34 | |
22 |