Erroneous results using ALL(CALENDAR[MonthName])

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