topic Why does removing a filter with ALL repeat values in a visual matrix? in DAX Commands and Tips

Why does removing a filter with ALL repeat values in a visual matrix?

CarlSagan — Fri, 28 Jul 2023 20:50:56 GMT

I'm using the contoso DB and I have a measure that removes the filter on product category.

Sales PCT Calculate 3 = CALCULATE( SUM(Sales[SalesAmount]), ALL('Product Category') )

My matrix has product category, subcategory on the rows. And the sum of sales amount with the measure above on the columns. When expanding the matrix to show the subcategory level, all the values are repeated for every category. Doesn't my measure mean the filters for the subcategory level are still in the filter context? Why the repeated values?

Re: Why does removing a filter with ALL repeat values in a visual matrix?

Take3 — Fri, 28 Jul 2023 21:38:58 GMT

CarlSagan

Yes, the filters for the subcategory are still in the filter context.

This is why you get different values for each subcategory (within a given category that is being ignored).

However, the ALL('Product Category') removes the filter on the Product Category.

As a result, each Product Category line in the the visual isn't going to reflect the number for the Product Category (but rather the number for ALL categories).

Correspondingly, each of the subcategories are NOT being filtered by the category either. So each subcategory is showing the total number for that subcategory across all categories. This is why the numbers repeat for each Category; because the calculation can't see the category.

Hope this is helpful to you.

Regards,

Nathan

Re: Why does removing a filter with ALL repeat values in a visual matrix?

CarlSagan — Fri, 28 Jul 2023 21:56:51 GMT

Perfect explanation, thanks!!