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.




v-echaithra's avatar
v-echaithra
Community Support
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.