ALL fails to clear filter context from row header

[Anonymous]

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:

Click this link to download the model.

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

[v-huizhn-msft]

Hi @Anonymous,

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

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

Please download the .pbix file for further analysis.

Best Regards,
Angelia

[Anonymous · Accepted solution]

Hi Angelia,

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

[Anonymous]

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.