Hello,
In CALENDAR table I have these columns, and others:
I have created these measures:
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!!!
Solved! Go to Solution.
Hi @JoanMF
Good questions 🙂
To explain what's going on here:
The behaviour in your visuals is then:
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
Hi @JoanMF
Good questions 🙂
To explain what's going on here:
The behaviour in your visuals is then:
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
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!
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!
User | Count |
---|---|
128 | |
65 | |
35 | |
27 | |
23 |
User | Count |
---|---|
142 | |
76 | |
42 | |
38 | |
21 |