Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JoanMF
New Member

Erroneous results using ALL(CALENDAR[MonthName])

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:

JoanMF_0-1643278013211.png

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

JoanMF_1-1643278353123.png

Does anyone know why the results are different?

Thank you!!!

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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!!!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors