Using ALL to ignore filters- strange results

[Anonymous]

Hi

I've created a dashboard with slicers on "Order Date" and "Category":

I'm trying to add 3 cards to the dashboard:

The first should ignore ignore all filters. It uses the following Measure:

Grand Total = CALCULATE([Sales Total],ALL(Sales[Customer]),ALL(Sales[OrderDate]),ALL('DATE _TABLE'[YearQuarter]))

The second should ignore the "Category" filter, but respect the "Date" filter. It uses the following Measure:

GrandTot DATES Selection = CALCULATE([Sales Total],ALL(Sales[Category]))

The third should respect the "Date" filter but ignore the "Category" filter. It uses the following Measure:

GrandTot CATS Selection = CALCULATE([Sales Total],ALL(Sales[OrderDate]),ALL('DATE _TABLE'[YearQuarter]))

Problem is:

• if I DON'T touch the "Date" slicer, the second card ignores any "category" selections;
• BUT if I DO change the "Date" slicer the second card DOES repsect the "Category" slicer

Example is at:

https://1drv.ms/u/s!AmxJyApgEAcYgtZqhTkd8PCAT0uJZQ

Any ideas?

Thanks!

[HotChilli]

Just to confirm, we are talking about the GrandTot DATES Selection = CALCULATE([Sales Total],ALL(Sales[Category]))

I've just tested by moving the date slicer to 4 Jan 2018 - 5 Jan 2017, the measure returns 13.38K.

'Bars' is already selected in the Category slicer.

In the Sales table, there are two records within the date period selected (I got lucky because one is in 'Bars' and one is in 'Crackers'

Ok, if the measure was respecting the Category filter, it should return only the 3971.88.

Do you agree with the test? What results are you getting?

[Anonymous]

Thanks for the rapid reply.

With your data selections I get the same results as you, which looks fine; however, if I select some different data, I get problems, e.g. if I select only data for January 2019:

If I don't select a category, the results are as I expect:

IfI Select "Bars", the result seems to respect the Category  filter, despite the ALL(Sales[Category])) code:

.. but if I select "Crackers" the result makes no sense at all! If Card 2 is respecting the filter I'd expect it to show £1.24K (i.e. £12,448.83)

Any ideas?

[tarunsingla]

You need to use the ALLEXCEPT function in your case.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

Removes all context filters in the table except filters that have been applied to the specified columns.

[Anonymous]

I've tried every permutation of ALLEXCEPT I can think of,  i.e.

1. GrandTot DATES S1 = CALCULATE([Sales Total],ALLEXCEPT(Sales,Sales[OrderDate]))

2. GrandTot DATES S2 = CALCULATE([Sales Total],ALLEXCEPT(Sales,'DATE _TABLE'[Date]))

3. GrandTot DATES S3 = CALCULATE([Sales Total],ALLEXCEPT('DATE _TABLE','DATE _TABLE'[Date]))

and tested these out in a new version at  https://1drv.ms/u/s!AmxJyApgEAcYgtZ_SYO2sCOxxabafg.

Results:.

(a) Base case - no Category filters, dates 4/1/19 to 30/01/19

S1 & S3 give the correct total. s2 ignores all filters and gives the grand total for the whole table:

(b) Change "Category" filter to "Bars" only

S1 & S3 calculate a result respecting the "Category" filter, which I don't want it to do!

(c) Change "Category" filter to "Cookies" only

S3 - same as (b) ; No idea where S1's answer comes from:

(d) No Category filters, change Date selection

I get the correct results in S1 & S3:

Any ideas/ advice?

Thanks!

[HotChilli · Accepted solution]

oldhasbeen,

I've just got back to looking at this and there is definitely an issue.  In my opinion there is nothing wrong with the original measure using ALL.

First of all, I think I have a solution to the issue . You have a DATE table in the model so get rid of the Order Date slicer and replace it with a slicer using Date from Date_Table.  (This is actually good practice since that is what Date tables are for). This should let the measure start working properly again. Let me know how you get on.

Now, the original issue.  I don't know if powerbi has always been like that but there is something unintuitive (or buggy) about using 2 slicers on the same table (one should be a date range slicer) along with a measure that uses ALL on the other field in the slicer. I'm going to investigate more but it looks to me that the 2 slicers filter each other but the date range slicer doesn't update to reflect the choice in the other slicer.  And the result on the measure does not reflect what's on screen. I'll get back to you on this or attempt to escalate to MS.

[Anonymous]

Hi HotChilli

This looks good!Thanks for your time & patience.

My solution is at https://1drv.ms/u/s!AmxJyApgEAcYgtcGRfnihvxdr4aWIA, if you are interested.

I'm a little surprised at your comment that there is  "something unintuitive (or buggy) about using 2 slicers on the same table ", it's something I've been doing with Excel Pivot Tables for years.

[HotChilli]

That's not the whole quote.

In the particular circumstances there does look to be something unintuitive.

Excel is different to powerbi of course.

Visuals affect each other unless 'Edit Interactions' is used