cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Lindino08
Frequent Visitor

Dax formula filtering on multiple tables not working correctly.

I am creating a new column to calculate the weighted average price for our products based on multiple filters.

I am getting what looks like a proper output when using this DAX formula. 

2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),
FILTER(ALLSELECTED('quote'), quote[Price List] = "2022 Price List"),
FILTER(ALLSELECTED('quote'), quote[statuscodename] <> "Revised"))

Lindino08_0-1675458556269.png

 

 

But as soon as I add in one more filter from my Quotedetails table the amounts are all the same.

2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),
FILTER(ALLSELECTED('quote'), quote[Price List] = "2022 Price List"),
FILTER(ALLSELECTED('quote'), quote[statuscodename] <> "Revised"),
FILTER(ALLSELECTED('quotedetail'), quotedetail[Option Upcharge?] = "No"))
Lindino08_1-1675458556235.png

 

 

Any help with this is greatly appreciated!

1 ACCEPTED SOLUTION
Lindino08
Frequent Visitor

I was able to resolve this by switching my relationship between table "QuoteDetail", and table "Products" to a many to many, from a many to one. Not sure why the many to one didn't work because I had direction set to both.

 

View solution in original post

3 REPLIES 3
Lindino08
Frequent Visitor

I was able to resolve this by switching my relationship between table "QuoteDetail", and table "Products" to a many to many, from a many to one. Not sure why the many to one didn't work because I had direction set to both.

 

marcelsmaglhaes
Super User
Super User

Hey @Lindino08 

The same value for all rows is because you are creating a column in quotetable and you put a filter in quote detail. There is relationship between this tables? If yes, wich type (1 to * or * to *)? Wich is the direction of the relationship (single or both). All these points can reproduce the same result for all rows. 

Please share more information about your data model. 

Best regards,
Marcel


Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI

If I've helped, don't forget to give Kudos and mark my post as a solution!
Imagem de fundo



Hi Marcel,

 

I am actually creating the new column in a table called "Products" and then filtering based on data from the "quote" table and "QuoteDetails" table. There is a one to many realationship between "Quote" and "QuoteDetail" and then a many to one relationship between "QuoteDetail" and "Products". Direction is set to both for all relationships.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors