cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oldhasbeen Regular Visitor
Regular Visitor

Using ALL to ignore filters- strange results

Hi

 

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

DAXALL1.PNG

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

 

DAXALL2.PNG

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
HotChilli Senior Member
Senior Member

Re: Using ALL to ignore filters- strange results

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.

7 REPLIES 7
HotChilli Senior Member
Senior Member

Re: Using ALL to ignore filters- strange results

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'CategoryRows4Jan2017.PNG

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?

oldhasbeen Regular Visitor
Regular Visitor

Re: Using ALL to ignore filters- strange results

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:

DAXALLT2 DATA.PNG

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

DAXALLT2 Base.PNG

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

 

DAXALLT2 BARS.PNG

 

.. 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)

 

DAXALLT2 Change.PNG

Any ideas?

 

tarunsingla Established Member
Established Member

Re: Using ALL to ignore filters- strange results

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.

oldhasbeen Regular Visitor
Regular Visitor

Re: Using ALL to ignore filters- strange results

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:
DAXALLEX BASE CASE.PNG
(b) Change "Category" filter to "Bars" only
S1 & S3 calculate a result respecting the "Category" filter, which I don't want it to do!
DAXALLEX BASE BARS.PNG
(c) Change "Category" filter to "Cookies" only
S3 - same as (b) ; No idea where S1's answer comes from:
DAXALLEX BASE COOKIES.PNG
(d) No Category filters, change Date selection
I get the correct results in S1 & S3:
 
DAXALLEX   JAN FEB.PNG
Any ideas/ advice?
 
Thanks!
 
 
 
Highlighted
HotChilli Senior Member
Senior Member

Re: Using ALL to ignore filters- strange results

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.

oldhasbeen Regular Visitor
Regular Visitor

Re: Using ALL to ignore filters- strange results

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 Senior Member
Senior Member

Re: Using ALL to ignore filters- strange results

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