topic Re: ALL function on a specific column not working like I think it should in DAX Commands and Tips

ALL function on a specific column not working like I think it should

beanboy — Sun, 09 Jun 2024 00:42:46 GMT

Hi there,

I have a table of SalesData which contains mainly normal sales transactions but some Warranty sales as well. I flag if the sale is a warranty or not in the WarrantyY/N column. I generally want to exclude warranty sales so I have a filter on all pages where WarrantyY/N = "N".

However, there are a few places where I still want to specifically show how many warranty sales there were, so I am looking for a measure to count the warranty sales transactions by ignoring the existing WarrantyY/N = "N" filter.

Perhaps I am understanding the ALL function wrong, but my current understanding is the ALL function when used on a specific column strips all filters on that column. Below is what I thought would work. I've tried a few iterations with using FILTER but same results. The visual counts warranties correctly with no other filters are on the WarrrantyY/N column, but if I apply a filter to equal "N", the visual blanks out, i.e. not stripping the filters. Again, I though the ALL function would ignor this page level filter.

#Sales_WARRANTIES = CALCULATE ( COUNT ( 'SalesData'[Date_Sale] ), ALL ( 'SalesData'[WarrantyY/N] ), 'SalesData'[WarrantyY/N] = "Y" )

I asked Copilot, and it also recommended the above solution.

There are some other page level filters, both from other columns in the SalesData table as wells as from related Dim tables if that makes a difference.

Re: ALL function on a specific column not working like I think it should

Jihwan_Kim — Sun, 09 Jun 2024 05:06:20 GMT

Hi,

I am not sure how your filter context is created on the page, but please try something like below whether it suits your requirement.

#Sales_WARRANTIES = CALCULATE ( COUNT ( 'SalesData'[Date_Sale] ), KEEPFILTERS ( FILTER ( ALL ( 'SalesData' ), 'SalesData'[WarrantyY/N] = "Y" ) ) )

Re: ALL function on a specific column not working like I think it should

xifeng_L — Sun, 09 Jun 2024 16:59:14 GMT

Hi beanboy

Did you apply sort by column to the WarrantyY/N field? If so, please add the sort reference field to the ALL function as well, for example:

ALL(WarrantyY/N, WarrantyY/N Index)

This is a side effect of the sort by column feature, which adds filtering of the sort reference field to the current context.

Here is a demonstration of that side effect:

Without using sort by columns, the ALL function gives the expected result.

After using sort by column, the results of the ALL function start to look strange.

And, add the sort reference field to the ALL function as well, the result returned to normal.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~