cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Guo_au
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

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.

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

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

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

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.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors