Sumx + All Selected is Not Removing Filter

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