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


View solution in original post

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!