topic Re: Why filter function changes values in a column in DAX Commands and Tips

Why filter function changes values in a column

mp390988 — Tue, 22 Apr 2025 13:27:58 GMT

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:

why does my table change from this:

to this:

I dont understand why the 23 gets replaced with 0

Thank You

Re: Why filter function changes values in a column

lbendlin — Tue, 22 Apr 2025 13:51:34 GMT

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.

Re: Why filter function changes values in a column

mp390988 — Tue, 22 Apr 2025 14:09:52 GMT

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 )

Re: Why filter function changes values in a column

mp390988 — Tue, 22 Apr 2025 14:18:19 GMT

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

Re: Why filter function changes values in a column

lbendlin — Tue, 22 Apr 2025 17:31:57 GMT

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.

Re: Why filter function changes values in a column

mp390988 — Tue, 22 Apr 2025 22:12:17 GMT

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 🙂

Re: Why filter function changes values in a column

v-echaithra — Wed, 23 Apr 2025 07:58:27 GMT

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:

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.

Re: Why filter function changes values in a column

mp390988 — Wed, 23 Apr 2025 09:53:52 GMT

HI v-echaithra ,

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

Re: Why filter function changes values in a column

v-echaithra — Thu, 29 May 2025 08:30:11 GMT

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.

Re: Why filter function changes values in a column

v-echaithra — Mon, 02 Jun 2025 05:06:03 GMT

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.

Re: Why filter function changes values in a column

v-echaithra — Wed, 04 Jun 2025 18:30:15 GMT

Hi mp390988 ,

Regards,
Chaithra.

Re: Why filter function changes values in a column

v-echaithra — Mon, 09 Jun 2025 05:20:45 GMT

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.