cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DimpleMehta
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

 

 

Please help me in making this kind of DAX.

 

 

 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@DimpleMehta 

 

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

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@DimpleMehta 

 

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

Please try my custom visuals

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

@DimpleMehta 

 

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

Please try my custom visuals

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors