cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Guo_au Frequent Visitor
Frequent Visitor

ALL fails to clear filter context from row header

Hi,

 

I noticed a strange behaviour of ALL function. Sometimes it fails to clear filter context from row header, as shown in the screenshot below:

Capture.JPG

Click this link to download the model.

 

Could anyone please explain why ALL works on [Month] but not [Month Name]?

1 ACCEPTED SOLUTION

Accepted Solutions
Guo_au Frequent Visitor
Frequent Visitor

Re: ALL fails to clear filter context from row header

Hi Angelia,

 

I think the answer to my question is in this post. It's due to how DAX deals with sorted column.

3 REPLIES 3
v-huizhn-msft Super Contributor
Super Contributor

Re: ALL fails to clear filter context from row header

Hi @Guo_au,

In your data table, the [Month Name] is based on [Month] column, so you need to ALL([Month]) in your formula. I create the measures using the formula below, and get expected result.

Max Date 2_test = CALCULATE(MAX(Calendar[Date]),ALL('Calendar'[Month]),ALL('Calendar'[Month Name]))

Row Count 2_test = CALCULATE(COUNTROWS('Calendar'),ALL('Calendar'[Month]),ALL('Calendar'[Month Name]))


4.PNG

Or you create [Month Name] using Calendar[Date] column rather than [Month] column. I create a calculated column using the formula.

month/name = FORMAT('Calendar'[Date],"MMM")&" "&YEAR('Calendar'[Date])

Max date = CALCULATE(MAX('Calendar'[Date]),ALL('Calendar'[month/name]))

Row Count = CALCULATE(COUNTROWS('Calendar'),ALL('Calendar'[month/name]))


5.PNG

Please download the .pbix file for further analysis.

Best Regards,
Angelia

Guo_au Frequent Visitor
Frequent Visitor

Re: ALL fails to clear filter context from row header

Hi Angelia,

 

Thank you for your reply. Could you please explain why ALL('Calendar'[Month Name]) by itself doesn't remove the filter context from row header? Why does ALL differentiate calculated columns based on an original column or another calculated column?

 

According to DAX Function Reference, ALL "Removes all filters from the specified columns in the table". It doesn't say ALL treats different types of columns differently. I found it very hard to use what's in the reference to explain what I observed in my attached model.

 

 

 

Guo_au Frequent Visitor
Frequent Visitor

Re: ALL fails to clear filter context from row header

Hi Angelia,

 

I think the answer to my question is in this post. It's due to how DAX deals with sorted column.