Forum Discussion
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.
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
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
Name Measure A 6 B 5 C 11 Total 22 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.
15 Replies
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.
- jdbuchanan71
Super User
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.
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.- jdbuchanan71
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
Name Measure A 6 B 5 C 11 Total 22 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.
- parry2k
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] )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
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.
- MFelix
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/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
