Dax Calculate Overriding of Filters

[meiwah]

Hi, I've a table Dates which contains dates from 01-Jan-23 to 31-Dec-23. The fields are Date, Year and Month Short

I've some measures to count the number of days in a year, before or on a certain end date, like 31-Jul-24

Days count 1 = Calculate(COUNT('Dates'[Date]), 'Dates'[Date]<=Date(2024,7,31), All(Dates[Month Short]))

Days count 2 = Calculate(COUNT('Dates'[Date]), 'Dates'[Date]<=Date(2024,7,31), REMOVEFILTERS(Dates[Month Short]))

Days count 3 = Calculate(COUNT('Dates'[Date]), 'Dates'[Date]<=Date(2024,7,31), All(Dates), Dates[Year]=SELECTEDVALUE(Dates[Year]))

Days count 4 = Calculate(COUNT('Dates'[Date]), 'Dates'[Date]<=Date(2024,7,31), ALLEXCEPT(Dates, Dates[Year]))

Days count 1 and Days count 2 return incorrect answers. Days count 3 and Days count 4 return the correct answers. 

My understanding is Days count 1 and Days count 2 should return the same answer as Days count 3 or Days count 4. Pls help to explain. My understanding is All(Dates(Month Short]) or Removefilters(Dates(Month Short]) should remove the Month Short filter from the matrix.

Thanks! I've been thinking for a few days but still can't grasp how it works 😅

[meiwah · Accepted solution]

Hi,

I finally found the reason for this problem. I copy the underlying query by opening the Performance Analyzer and pasting in Dax Studio.

Found that there exists another filter on the column [MonthofYear], which is the sort by for column [Month Short]

[meiwah]

Hi BBF,

Thanks for your reply! For measure 1 and measure 2, the removal of filter on Dates[Month Short]) is intended, so the measures should return the no. of days in a year, for dates before 31-Jul-24. But as reflected in the results, the filter on Dates[Month Short]) from the matrix is still on, it is not removed, that is the reason the no. of days in a month is returned instead of number of days in a year. What I need is the number of days in a year. Thanks!

[BeaBF]

@meiwah Hi!

In measure 1, you are using ALL(Dates[Month Short]), which removes the filter applied to the 'Dates' table on the 'Month Short' column. This effectively ignores the filtering context of the month when calculating the count. Therefore, it will count all dates on or before 31-Jul-2024, regardless of the month, leading to an incorrect result.

Measure 2 uses REMOVEFILTERS(Dates[Month Short]), which removes all filters applied to the 'Month Short' column. Again, this removes the context of the month, leading to an incorrect result for the same reasons as Days count 1.

In measure 3, you use ALL(Dates) to remove all filters applied to the 'Dates' table, and then you explicitly filter it by 'Year'. This correctly calculates the count of days on or before 31-Jul-2024, considering the selected year, which results in the correct count.

Days count 4 uses ALLEXCEPT(Dates, Dates[Year]), which removes all filters from the 'Dates' table except the filter on 'Year'. This also correctly calculates the count of days on or before 31-Jul-2024, considering the selected year, resulting in the correct count.

In summary, Days count 3 and Days count 4 are correct because they retain the necessary context of the selected year while applying the date filter, whereas Days count 1 and Days count 2 remove the context of the month, leading to incorrect results.

BBF