Forum Discussion
ALL/ALLEXCEPT problem
You are indeed correct, its using the 'Calendar'[DayNumberOfWeek] column. The reason is that the 'Calendar'[DayName] column is sorted by 'Calendar'[DayNumberOfWeek], so Power BI has to include that in the underlying summary table so that it can display it correctly in the visual.
If you add a REMOVEFILTERS or ALL on 'Calendar'[DayNumberOfWeek] to your measure as well as the existing one on 'Calendar'[DayName] then that should give you the results you are expecting.
In a table or matrix visual, the column and row headings are applied to each individual cell to calculate the value for that combination of filters, so if you had months in the row, and day of week in the columns, then for each cell it would use ( month, day of week ) as a combined filter.
Possibly the best way to think of sort columns is as if they were hidden headers, so they too get included in the filter, so in my previous example the filters applied would actually be ( month, month number, day of week, day of week number ) as each of the base columns requires a sort column to be included.
That worked indeed johnt75, thank you! What I'm just not following is why PowerBI is using DayNumberOfWeek as a filter, since it's only used for sorting. So I would rather expect that the outcome would be the same, but the order might change or something. Is sorting considered also as some kind of sorting to then? I always like to understand why something is happening, so if you wouldn't mind giving me a little bit more insight? Many thanks again.
Gr. DLU
In a table or matrix visual, the column and row headings are applied to each individual cell to calculate the value for that combination of filters, so if you had months in the row, and day of week in the columns, then for each cell it would use ( month, day of week ) as a combined filter.
Possibly the best way to think of sort columns is as if they were hidden headers, so they too get included in the filter, so in my previous example the filters applied would actually be ( month, month number, day of week, day of week number ) as each of the base columns requires a sort column to be included.