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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.