Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |