Forum Discussion
Post PartisanFilter vs Calculatetable - different behaviour resulting in different answers
I have the following DAX formula inside a calculated column:
WorkingDays = var lastDateOfMonth = ENDOFMONTH(dimDate[Date]) var startDate = date(dimDate[Year], dimDate[MonthNum], 1) var endDate = date(dimDate[Year], dimDate[MonthNum], DAY(lastDateOfMonth))
var holidays = FILTER( DISTINCT(dimDate[date]), dimDate[IsHoliday]=1 ) return NETWORKDAYS(startDate,endDate, 1, holidays)
WorkingDays = var lastDateOfMonth = ENDOFMONTH(dimDate[Date]) var startDate = date(dimDate[Year], dimDate[MonthNum], 1) var endDate = date(dimDate[Year], dimDate[MonthNum], DAY(lastDateOfMonth))
var holidays = CALCULATETABLE( DISTINCT(dimDate[date]), dimDate[IsHoliday]=1 ) return NETWORKDAYS(startDate,endDate, 1, holidays)
Can someone please explain to me what is going on here?
Why does the FILTER one give 0 whilst CALCULATETABLE gives 22 when DimDate[Date] = 01/01/2025?
Thank You
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
11 Replies
johnt75Super User
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 )- mp390988
Hi johnt75 ,
Thank you for you explanation.I agree with you when you say the syntax for the version using FILTER is incorrect.
I myself explored the below code by simply creating a table and I also got an error.var holidays = FILTER( DISTINCT(dimDate[date]), dimDate[IsHoliday]=1 )But I understand your point that it works in the calculated column because of row context.
But I still don't understand how the FILTER version returns a 0 whilst the CALCULATETABLE version returns 22 for the first row i.e. date = 01/01/2025
The NETWORKDAYS should return the difference between start date = 01/01/2025 and end date = 31/01/2025 excluding the holidays.- johnt75
The 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.
- Greg_Deckler
Community Champion
mp390988 Broadly, CALCULATETABLE replaces filter context while FILTER adds filter context. It's a subtle difference and you really need to know what you are doing when you use CALCULATETABLE (and CALCULATE). They are the same function only that the former returns a table while the latter returns a scalar. I would need sample data to test this out but CALCULATE and CALCULATETABLE are black boxes that you can't really debug, you simply have to "know" how they work but how they work is stupidly complex.
Do you by chance have 22 dates flagged as IsHoliday?
- v-csrikanth
Community Support
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 - v-csrikanth
Hi mp390988
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi mp390988
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.
If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi mp390988
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!
Best Regards,
Community Support Team _ C Srikanth.
