Filter vs Calculatetable - different behaviour resulting in different answers

[mp390988]

Hi,

I have the following DAX formula inside a calculated column:

Code:

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)

then I get this:

If I change the formula to replace FILTER with CALCULATETABLE as per below:

Code:

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)

then I get this:

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

[johnt75 · Accepted solution]

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 )

[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.

[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 

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 · Accepted solution]

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

[johnt75 · Accepted solution]

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]

Hey @johnt75 ,

Doesn't the REMOVEFILTERS(DimDate) also remove the filter IsHoliday=1 as well, so in the end we have all the dates including those that have IsHoliday=0?

[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.

[mp390988]

Hi @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.

[Greg_Deckler]

@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?

[mp390988]

Thank you for your reply.

I don't have 22 dates flagged as IsHoliday, only 8.
Here is the pbix file file 

Thank You