DAX for sum of one column based on column in another table

[Anonymous]

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.

[Zubair_Muhammad · Accepted 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]

@Zubair_Muhammad Thanku so much!! It works!!  Can you please explain this Treatas DAX littlebit.

[Zubair_Muhammad]

@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