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.




mp390988's avatar
mp390988
Post Partisan
1 year ago

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

    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 🙂