Why filter function changes values in a column

[mp390988]

Hello,

I have this DimDate table I created in Power BI Desktop using the Calendar function.

I expanded this table to add a column that counts the number of business/working days per month using the NETWORKDAYS

I am trying to enhance my NETWORKDAYS formula by incoporating holidays.

So I tried creating the following variable to bring back a table containing only a list of dates that are declared as holidays using the "IsHoliday" field in my table and then I am passing this in my NETWORKDAYS formula.

var holidays = FILTER(
    DISTINCT(dimDate[date]),
    dimDate[IsHoliday]=1
)

When I add this variable in my NETWORKDAYS formula so like below:

BEFORE:

WorkingDays = 

var lastDateOfMonth = ENDOFMONTH(dimDate[Date])
var startDate = date(dimDate[Year], dimDate[MonthNum], 1)
var endDate = date(dimDate[Year], dimDate[MonthNum], DAY(lastDateOfMonth))

return NETWORKDAYS(startDate,endDate)

AFTER:

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)

why does my table change from this:

to this:

I dont understand why the 23 gets replaced with 0

Thank You

[v-echaithra]

Hi @mp390988 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @mp390988 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @mp390988 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra · Accepted solution]

Hi @mp390988 ,

In a calculated table or as a table expression in a visual, where IsHoliday cannot be resolved to a single value, especially if there's row context but no aggregation or the column is ambiguous across multiple rows.
This works in your workingdays Formula because you are writing this as a calculated column, meaning DAX evaluates this row by row.
The formula is calculated for each row of the dimDate table.
Within each row, dimDate[IsHoliday] has a known value — either 1 or 0.
So the engine assumes that dimDate[IsHoliday] means at this row, it’s just this one value.

Even though your holidays variable seems to reference a wider context, it inherits the current row context, which can be misleading. This leads to a subtle bug, FILTER(DISTINCT(dimDate[Date]), dimDate[IsHoliday] = 1) might evaluate to nothing at all, which is why your results may return 0 or unexpected values.

Fix it by replacing DISTINCT(dimDate[Date]) with ALL(dimDate) or use an external holiday table.

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra.

[v-echaithra]

Hi @mp390988 ,

Try using the all function to remove row context from the dimdate table when filtering holidays. This allows the holiday filter to see the full table rather than just the current row:

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(
    ALL(dimDate),
    dimDate[IsHoliday]=1
)

return NETWORKDAYS(startDate,endDate, 1, holidays)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Regards,
Chaithra.

[mp390988]

HI @v-echaithra ,

Thank you for suggestion, I tried it but has no effect.

[mp390988]

Trying to debug my code so I thought let me see what the below code returns by creating a table

var holidays = FILTER(
    DISTINCT(dimDate[date]),
    dimDate[IsHoliday]=1
)

I get this:

 But when I use it in my WorkingDays formula (defined in my last message) it doesn't complain. Weird

[mp390988]

Hi,

Are there any DAX gurus out there that can explain to me what the below formula does when it gets to the FILTER part? I don't understand how it gets 0 for the WorkingDays when date = 01/01/2025 and I suspect its something to do with the FILTER function part.

Thank You 🙂

[lbendlin]

I have this DimDate table I created in Power BI Desktop using the Calendar function.

Obligatory note:  Calendars are immutable. There is no need to compute them over and over again, either in Power Query or in DAX.  Yes. it's a rite of passage kind of thing, but it is unnecessary.  Use a static external reference table.

How did you define the [IsHoliday] column?  Usually you have another external reference table that lists the holidays per country, state, county, or city,  and you use that table directly in the NETWORKDAYS function.

Note2:  Using NETWORKDAYS in a calculated column is not really necessary (see above), it should really only be used in measures.

[mp390988]

Hi,

I am praticing at home so don't have a datawarehouse where I can pick up a dimension table for dates. So I created one manually using DAX but I understand your point, of course if I was working for a company I would use one of their company wide date tables.

So my IsHoliday was created as per below:

IsHoliday = SWITCH(
    TRUE(),
    dimDate[Date] = DATE(2025,1,1), 1,
    dimDate[Date] = DATE(2015,4,18), 1,
    dimDate[Date] = DATE(2025,4,21), 1,
    dimDate[Date] = DATE(2025,5,5), 1,
    dimDate[Date] = DATE(2025,5,26), 1,
    dimDate[Date] = DATE(2025,8,25), 1,
    dimDate[Date] = DATE(2025,12,25), 1,
    dimDate[Date] = DATE(2025,12,26), 1,
    0
)

[lbendlin]

Try this as a calculated table:

Holidays = DATATABLE("Date",DATETIME,{{"2025-01-01"},{"2025-04-18"},{"2025-04-21"},{"2025-05-05"},{"2025-05-26"},{"2025-08-25"},{"2025-12-25"},{"2025-12-26"}})

Note: You had a 2015 date in your sample data.