Why is DAX multiplying rows when removing filters with ALL?

[AdamMarczak]

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 

[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/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

[jdbuchanan71]

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.

[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] 
)

[AdamMarczak]

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.

[AdamMarczak]

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?

[parry2k]

@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.

[AdamMarczak]

@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.

[MFelix]

Hi @AdamMarczak ,

When having tables related you need to understand that you don't need to make the use of filter to make the calculations, if you get the context from the correct table the use of the filters especcially ALL will only give you unexpected results.

In you case a simple measure like SUM and the use of the manufacturer name from the table will give you the correct calculation no need to make this complicated.

Regards,

MFelix

[AdamMarczak]

@MFelix in my case I need to have manufacturer and area columns in my table so my context is too narrow so I need to drop some filters. I can't do this without ALL since my filter context is already A, Adam, 1 so my SUM will return 1 if I dont drop filter on Adam (Manufacturer) and 1 (Area).

@parry2k I did, it kinda broke my mind but I guess it works really well because of how divide as IF BLANK return 1 otherise return blank. Clever. I have 600 to 2 TB models so I will test the performance. Clever trick indeed. Thanks!

[parry2k]

@AdamMarczak the calculation I gave you will take care of it, did you tested it?

[AdamMarczak]

@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. 

[AdamMarczak]

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 · Accepted solution]

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.

[AdamMarczak]

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.