cancel
Showing results for
Did you mean:
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.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
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

 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.

14 REPLIES 14
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

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

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.

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

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

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

```Total Area = SUM( Fact[Area] )

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

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

Super User

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

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

Proud to be a Datanaut!

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

 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.

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

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.

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?

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.

Announcements

#### 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

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

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)