Erroneous results using ALL(CALENDAR[MonthName])

[JoanMF]

Hello,

In CALENDAR table I have these columns, and others:

• Month = MONTH(CALENDAR[Date])
• MonthName = FORMAT(CALENDAR[Date];"mmmm")

I have created these measures:

• NumOrders = COUNT(Orders[OrderId])
• Orders ALL Months = CALCULATE(NumOrders;ALL(CALENDAR[Month]))
• Orders ALL MonthsName = CALCULATE(NumOrders;ALL(CALENDAR[MonthName]))

Theoretically, I think the results of both measures should be the same, but no... You can see in this matrix, for example:

And another problem! If MonthName is used in the hierarchy, instead of Month, the result is:

Does anyone know why the results are different?

Thank you!!!

[OwenAuger · Accepted solution]

Hi @JoanMF 

Good questions 🙂

To explain what's going on here:

1. The modifiers ALL ( <column> ) or REMOVEFILTERS ( <column> ) remove filters from the specific column specified.
   • So your measure [Orders ALL Months] removes filters from just the column 'CALENDAR'[Month].
   • And [Orders ALL MonthsName] removes filters from just the column 'CALENDAR'[MonthName].
2. When a Sort By Column is specified for a column, the Sort By Column is automatically included in the DAX query (created by Power BI behind the scenes) when the original column is included in a visual, and in effect behaves as a sort of "hidden filter" in addition to the column actually displayed in the visual.
   • It appears that 'CALENDAR'[MonthName] has 'CALENDAR'[Month] as its Sort By Column, quite rightly so that the month names are sorted in calendar order.
   • As a result, when 'CALENDAR'[MonthName] is included on a visual, 'CALENDAR'[Month] is automatically included in the DAX query and becomes an additional filter.
   • When filters on 'CALENDAR'[MonthName] are removed, filters on 'CALENDAR'[Month] remain, giving an unintended result. You see the same result as the original [NumOrders] measure.

The behaviour in your visuals is then:

1. In the first table, [Orders ALL Months] removes the filter on 'CALENDAR'[Month] and you see the annual total as expected.
   However, [Orders ALL MonthsName] has no filters to remove on 'CALENDAR'[MonthName], and ALL has no effect. The filter on 'CALENDAR'[Month] remains unchanged.
2. In the second table, since MonthName is used in the visual, the Sort By Column Month is added behind the scenes.
   Each of the measures [Orders ALL Months] and [Orders ALL MonthsName] removes filters from only a single column. Since both MonthName and Month are effectively filtered, in both cases a filter on either MonthName or Month remain in effect, and the monthly values returned by the measures are unchanged from [NumOrders].

What to do about this?

1. When removing filters on a column that has a Sort By Column, always remove filters on the Sort By Column as well.

(See this article for a good description.)

For example, this would mean rewriting Orders ALL MonthsName as:

Orders ALL MonthsName = 
CALCULATE ( 
    [NumOrders],
    ALL ( 'CALENDAR'[Month], 'CALENDAR'[MonthName] )
)

2. If the intention of the [Orders ALL Months] (and similar) measures is to calculate a total for the selected year(s), ignoring any sub-year filtering, consider rewriting in a more generic way such as this:

Orders Annual Total = 
CALCULATE (
    [NumOrders],
    REMOVEFILTERS ( 'CALENDAR' ),
    VALUES ( 'CALENDAR'[Year] )
)

This measure removes all filters from 'CALENDAR', but then restores the visible Years as a filter.

(I acknowledge that in the time I have taken to type this, someone else may well have replied, perhaps more succinctly 🙂 )

Regards,

Owen

[JoanMF]

Thanks again, OwenAuger.

Now I have been analyzing your entire explanation, and it is very clear, it has helped me a lot to understand  filters when you use Sort by column. I imagine the same thing happens if you create columns with WEEKDAY and FORMAT(CALENDAR[Date];" dddd").

Thank you very much again!!!

[JoanMF]

I just tried the first option, and it works! Thanks a lot!