Forum Discussion
Post PartisanFilter vs Calculatetable - different behaviour resulting in different answers
This is rather intricate. Your syntax for the version using FILTER is incorrect. The columns you reference in the filter conditions must be included in the table you are trying to filter, and in this case dimDate[IsHoliday] is not included in the table generated by DISTINCT(dimDate[date]). Indeed, if you run
EVALUATE FILTER ( DISTINCT ( dimDate[date] ), dimDate[IsHoliday] = 1 )in DAX Query View you will get an error saying that a single value for IsHoliday cannot be determined.
The reason you don't get an error when running it in the calculated column is because a single value can be determined - in a calculated column you have a row context, which means the filter condition is checking the value of IsHoliday in the current row, not in the table you are trying to filter.
The reason that the version with CALCULATETABLE almost works is because you can specify a column which is not included in the base table. However, because you are retrieving values from the table in which you have a row context, because you are in a calculated column, you need to remove the existing filters generated by the row context.
Finally, you don't need to generate a variable for end date, you can just use the last day of the month. The final code should be
WorkingDays = VAR lastDateOfMonth = EOMONTH ( dimDate[Date], 0 ) VAR startDate = DATE ( dimDate[Year], dimDate[MonthNum], 1 ) VAR holidays = CALCULATETABLE ( DISTINCT ( dimDate[date] ), dimDate[IsHoliday] = 1, REMOVEFILTERS ( dimDate ) ) RETURN NETWORKDAYS ( startDate, lastDateOfMonth, 1, holidays )Hi mp390988
No, REMOVEFILTERS(dimDate) clears existing filters from the dimDate table before applying new filters inside CALCULATETABLE.
The condition dimDate[IsHoliday] = 1 is still active and reapplied after the REMOVEFILTERS step inside CALCULATETABLE's logic.
Thus, the final result will correctly include only dates where IsHoliday = 1, not all dates.
Best Regards,
Cheri Srikanth
Super UserThe FILTER returns 0 for rows where IsHolilday is 1 because the logic is saying "if IsHoliday for the current row is 1 then return every value from Date regardless of whether it is a holiday or not". So every value from the date table is added to the holidays variable, and when you call NETWORKDAYS every date is flagged as a holiday and so there are no working days at all.
Without the REMOVEFILTERS, the CALCULATETABLE version performs context transition, so the value of every column is put into the filter context, which means that only that specific row is visible. The filter for IsHoliday =1 will only return true when the row is itself a holiday, which is why the value for Jan 1 is correct ( 22 ) and the value for all other days in Jan is not correct ( 23 ) - no other days in Jan can see the holiday on Jan 1 and so it is not added to the list of holidays.
You can see this by changing the column to return COUNTROWS(holidays) both with and without the REMOVEFILTERS.
Post PartisanHi johnt75 ,
Thank you for your explanation. I think I get it.
My understanding of how the FILTER function works was the issue.
I always thought that the table inside the FILTER i.e. FILTER(table, expression) inherits the filters coming from the row context. So for the first row in the calculated column where dimDate[date] = 01/01/2025 I then thought this filters the table inside the FILTER function to show only one record and that is of the date = 01/01/2025. My rational for this thinking was because the expression part of the filter function does get filtered to the current row in the calculated column so I thought the table part of the FILTER function also did as well.