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.
The ' surrounding Calendar is because CALENDAR is a DAX function, so the ' is necessary to distinguish your Calendar table from the function. Don't worry about, it isn't causing the problem.
It sounds like more filters are being applied than you are aware of, Power BI sometimes creates local date tables, which you can't see in Power BI Desktop, and it could be applying a filter to that.
To see exactly what filters are being applied, open Performance Analyzer from the View ribbon, click Start Recording and then Refresh Visuals. Expand the entry for your matrix visual and copy the code. You can then paste it into DAX Studio, or any text editor, and examine exactly which filters are being applied.
Dear johnt75,
Thank you for your quick response. I've followed the steps like you described and this was the outcome within DAX Studio:
// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('Calendar'[DayName], 'Calendar'[DayNumberOfWeek]), "IsGrandTotalRowTotal"
),
"Total_Sales", 'Sales'[Total Sales],
"Total_Sales_for_All_Days_Selected_Dates", 'Sales'[Total Sales for All Days Selected Dates],
"v__Sales_for_All_Days_Selected_Dates", 'Sales'[% Sales for All Days Selected Dates],
"Total_Calendar_Sales", 'Sales'[Total Calendar Sales],
"Total_Days_Names_Sales", 'Sales'[Total Days Names Sales]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Calendar'[DayNumberOfWeek],
1,
'Calendar'[DayName],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'Calendar'[DayNumberOfWeek], 'Calendar'[DayName]
Am I interpreting this correctly that it also does something with Calendar DayNumberOfWeek? If so, could you explain to me why this is happening and what it's doing? And just for your information. The calendar table isn't one created with DAX, it was imported from an excelfile. And I've also disabled the ability for PowerBI to create it's own datetables. Many thanks again!
Gr DLU
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.
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.