cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamMarczak Frequent Visitor
Frequent Visitor

Why is DAX multiplying rows when removing filters with ALL?

Hello All,

I did a lot of googling but I wasn't able to find proper answer. 

What is happening behind the scenes, that when writing this dax 

 

Measure = CALCULATE(SUM('fact'[Area]), ALL(Prod[Manufacturer]), ALL('fact'[Area]))

And having One to Many relationship between Prod (1) and Fact (many) I get this result.

Annotation 2019-07-18 001327.png

 

Even though product C has only relationship to prod with ID 3 manufactured by John after I run ALL(Manufaturer) it is as a cross join would be happening. Why is that and how to avoid it other than writing silly queries which do IF count rows or something like this.


What I'm trying to calculate is total over current NAME.

Adding file as example https://1drv.ms/u/s!AgYxU_sdmx4vjeMD1lyipICqAJFQzA?e=OZlfLD 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Why is DAX multiplying rows when removing filters with ALL?

A matrix visual will return a row for every line where the measure is not blank.  Your ALL change the calculation to be the same as if you removed Manufacturer and Area from your visual.  It would return something like 

 

NameMeasure
A6
B5
C11
Total22

 

The measure amount is ignoring Manufacturer and Area so the measure returns an amount on all combinations.  The join applies a filter, ALL removes the filter.

View solution in original post

14 REPLIES 14
AdamMarczak Frequent Visitor
Frequent Visitor

Re: Why is DAX multiplying rows when removing filters with ALL?

Just wanted to add that I traced this with DAX studio and I know it does LEFT OUTER JOIN underneath that is why I get rows without match on PROD table. But I want to understand how to remove filters on PROD without changing left join to left outer join.

 

Super User
Super User

Re: Why is DAX multiplying rows when removing filters with ALL?

Hello @AdamMarczak 

Give this change a try.

IF ( NOT ISBLANK ( SUM('fact'[Area] ),
CALCULATE(SUM('fact'[Area]), ALL(Prod[Manufacturer]), ALL('fact'[Area]))
)

This will limit the totaling measure to only dispaly on rows with an amount in area.

AdamMarczak Frequent Visitor
Frequent Visitor

Re: Why is DAX multiplying rows when removing filters with ALL?

Hey,

I do know it will fix it. I just wanted to understand why is this happening really, And if NON BLANK, COUNTROWS or any other method is really best choice from performance standpoint.

Super User
Super User

Re: Why is DAX multiplying rows when removing filters with ALL?

@AdamMarczak if can be costly you can do something like this.

 

Add two measures

 

Total Area = SUM( Fact[Area] )

Area Measure = 
CALCULATE(
[Total Area], ALL(Prod[Manufacturer]), ALL('fact'[Area])) *
DIVIDE( [Total Area], [Total Area] 
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User
Super User

Re: Why is DAX multiplying rows when removing filters with ALL?

Hi @AdamMarczak ,

 

When you have tables with one to many relationships the way you place the information on your visual is important because if you notice the filter path is from the one side to the many side, meaning that if you want to filter information is filter from a single point of the table.

 

The ALL syntax returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table, meaning that in your case when you make the ALL place in your calculation the rows of the manufacturer are going to be shown even if they don't have any value as you can see in the 5th , 7th and last row of the table.

 

So the use of ALL ignores the filter context and returns that value of the table so returning you the name of all your suppliers.

 

Please check the links below with further explanation on the use of ALL syntax.

 

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

 

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

 

Be aware that for calculation with relationship you do not need to use the ALL function if you grab the correct fields the calculation will be corret based on context.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Why is DAX multiplying rows when removing filters with ALL?

A matrix visual will return a row for every line where the measure is not blank.  Your ALL change the calculation to be the same as if you removed Manufacturer and Area from your visual.  It would return something like 

 

NameMeasure
A6
B5
C11
Total22

 

The measure amount is ignoring Manufacturer and Area so the measure returns an amount on all combinations.  The join applies a filter, ALL removes the filter.

View solution in original post

AdamMarczak Frequent Visitor
Frequent Visitor

Re: Why is DAX multiplying rows when removing filters with ALL?

I think I figured out the answer though while you guys helped 🙂

 

ALL joins in DAX are LEFT OUTER JOINs which means my table underneath is always

Annotation 2019-07-18 003844.png

 

But DAX before returning me the rows will remove B | John, C | Adam and C | Tom because they have BLANKs on rows. When I added ALL I explicitly changed formula that on those three rows DAX was able to calculate values there and as such it gave impression of rows multiplication. In reality it never multiplied rows, it was just how backend works.

AdamMarczak Frequent Visitor
Frequent Visitor

Re: Why is DAX multiplying rows when removing filters with ALL?

And btw thanks for prompt answers! 

What is the best BLANK check in terms of performance? Would it be SUM or maybe COUNTROWS, or maybe something entirely different?

AdamMarczak Frequent Visitor
Frequent Visitor

Re: Why is DAX multiplying rows when removing filters with ALL?

@MFelix  thank you for this. I did read both of those but I think I missed the idea a bit.

I accepted @jdbuchanan71 answer because his explanation is much more clear on what is happening. 

Also please note with fantastic video from Marco Russo https://www.youtube.com/watch?v=teYwjHkCEm0 it is not correct to say ALL returns all rows. Because ALL simply removes filter from specified column (or columns). But it might be just wording, your thinking is correct of course. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)