Forum Discussion

mp390988's avatar
mp390988
Post Partisan
1 year ago
Solved

Why filter function changes values in a column

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 ...
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    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.




lbendlin's avatar
lbendlin
Super User
1 year ago
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's avatar
    mp390988
    Post Partisan
    1 year ago

    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's avatar
      lbendlin
      Super User
      1 year ago

      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.