cancel
Showing results for 
Search instead for 
Did you mean: 
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!

My Blog
Connect on Twitter
Connect on 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!

My Blog
Connect on Twitter
Connect on 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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!