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.
Super UserHello 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.
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?
