cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Community Champion

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

3 REPLIES 3
Community Champion

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

Frequent Visitor

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

Community Champion

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

Regards
Zubair

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.