Forum Discussion
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
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.
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.
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?
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.- parry2k
Super User
AdamMarczak I would stay away from using IF condition as it can be expensive and can slow down the DAX calculation. May be on smaller dataset it is not a big but complex/larger dataset, it can be costly.
parry2k understand but i wonder how to avoid mentioned scenario in this case. I need to drop filters from related table and calculate SUM over Name column. How do i check for blank so I don't get empty rows like here.
- parry2k
AdamMarczak the calculation I gave you will take care of it, did you tested it?
