topic Re: DAX for sum of one column based on column in another table in DAX Commands and Tips

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

Anonymous — Mon, 01 Jul 2019 09:23:05 GMT

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.

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

Zubair_Muhammad — Mon, 01 Jul 2019 10:45:40 GMT

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] )
)

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

Anonymous — Mon, 01 Jul 2019 10:55:28 GMT

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

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

Zubair_Muhammad — Mon, 01 Jul 2019 11:09:26 GMT

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