Sumx + All Selected is Not Removing Filter

[Anonymous]

Hi, 

I'm trying to understand why the combination of SUMX + ALL vs ALL SELECTED are giving a different values.

My understanding is this that the two should only be different if there are external filters applied.

Is there a reason why this is happenning? Is there any suggestions on how the SUMX + ALL SELECTED formula can be corrected as I would ideally like the table to be dynamic with user selection.  Ie. If product 1 is filtered the ALL SELECTED  + SUM X should be the SUM of all Product 1 Y1 Units (jan - Dec) in every row.

If I run a similar formula using SUM + ALL SELECTED it seems fine.

DAX Below:

ALL + SUM X = SUMX('For forum', Calculate(

         SUM('For forum'[Y1 Units]),

         ALL('For forum')))

ALL + SUM X = SUMX('For forum', Calculate(

        SUM('For forum'[Y1 Units]),

        ALL('For forum')))

 

Thanks,

Alex 

[v-alq-msft]

Hi, @Anonymous 

ALL is a useful function that returns all the rows of a table or all the values of a column, depending on the parameter you use. In all of its variations, ALL ignores any existing filter to produce its result. You can use ALL as an argument of an iteration function, such as SUMX and FILTER, or as a filter argument in a CALCULATE function. 

ALLSELECTED is a very useful function whenever you want to perform computations using the pagefilter or slicer selections in the pivot table as one of the parameters. What you need here is a function that does not return all of the 'For forum', but only the ones selected in the original filter context, that is, the one of the complete pivot table. We call this kind of computation Visual Totals, because it uses as the grand total that is visible to the user instead of the total of the complete data model. In fact, ALLSELECTED removes from the context the last filter generated from context transition(Sumx, Averagex,filter...).  It will remove the more restrictive filter (inner), leaving the outer one working.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Refer :

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi

[TomMartens]

Hey @Anonymous ,

I'm not sure if you are aware of this article from the guys at sqlbi.com that explains the intricate workings of ALLSELECTED: https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Maybe I'm wrong, but it seems that both of the DAX formulas are the same.

Please provide a sample pbix that contains the data, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

Thanks,  Everyone Ill take a look at the links shared.

In the meantime below is the link to the power BI file I was refering too

https://www.dropbox.com/sh/3q4iir5qcjx59gv/AADYjeOvYJnsCzohWXckvX6ra?dl=0

Thanks,

Alex 

[Anonymous]

Hi All,

I made a few attempts but wasn't able to resolve the issue and didn't quite understand the note of shadow filter context when using SUMX and ALL SELECTED.

Wondering if anyone would be able to help or provide some clarity on what is happening.

https://www.dropbox.com/sh/3q4iir5qcjx59gv/AADYjeOvYJnsCzohWXckvX6ra?dl=0

Thanks,

Alex 

[Anonymous]

https://www.dropbox.com/sh/3q4iir5qcjx59gv/AADYjeOvYJnsCzohWXckvX6ra?dl=0

Thanks,

Alex 

[TomMartens · Accepted solution]

Hey @Anonymous ,

thanks for providing the link to the DROPBOX again.

There is a subtle difference between ALL and ALLSELECTED but before I will cover this in more detail. I will explain what's happening by using SUMX. SUMX as all the other ...X functions is called an iterator function. Meaning the numeric expression (2nd parameter) will be executed as often as there are rows in the table, that is passed to one of the iterator functions (here SUMX) as 1st parameter.

In both examples, the 1st parameter (the table), is the table reference 'For Forum'. Referencing a table that way means the filtered table is referenced (https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html).

It's necessary to always know what the filtered table is!

Most often there are some slicers involved because the pbix you provided does not contain any slicers we must not get confused and think the table 'For Forum' is not filtered - the table is filtered. The filter context both measures have to consider are applied by the columns used: Product, Month, and Y1 Units. We must not forget that even a numeric column (Y1 Units) is "contributing" to the filter context.

One might consider ALL as a crowbar and ALLSELECTED as a scalpel when manipulating the existing filter context.

Using ALL inside the numeric expression removes all existing filters that are currently applied. For this reason, each row of the ALL measure returns ~4118 (the sum of the numeric column Y1 Units), this ~98851 for the Total line (24-rows time 4118).

Now ALLSELECTED, ALLSELECTED removes the last applied filter to the referenced object in a given context (something different than the filter context). In the sample pbix this context is provided by the table visual. It's possible to imagine a filter as a combination of columns {Product:Product 1 | Month:Apr | Y1 Units:318.35}. Now it's necessary to be aware that there are 24 of these filters in the given context (the table visual), removing the last one that has been applied, leaves 23 other filters in place.

One final word, to be precise: ALL, ALLSELECTED, ALLEXCEPT, does not remove existing filters, these functions are blocking the application of filters (see the article by Jeffrey Wang from above). Sometimes this subtle, almost meaningless distinction helps to understand why something is not working or why a small change to an existing measure does not change anything.

Hopefully, this helps to better understand what's going on.

Regards,

Tom