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

View solution in original post

3 REPLIES 3
Microsoft v-huizhn-msft
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
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.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors