Incorrect result by using different filter expression value

[Nicolachan0416]

Hi everyone, 

I am new to the PowerBI soultion and I have a question about the DAX filter function and causing incorrect result.

It is applied the same formula, and i just change the filter value to "new" or "term". In the returning value, YTD New Hire is correct, however the YTD Exit is incorrect to show "Blank" when it sums the headcount column. Do you have any idea how can i fix this problem?

Here is the screenshots:

Thanks.

[Nicolachan0416]

Thanks, and i tried to use the slicer with Master_IN_Out[Period], then i have no problem.

[Anonymous]

Hi, @Nicolachan0416 

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777 · Accepted solution]

Hi @Nicolachan0416 ,

The issue with your YTD Exit measure returning (Blank) while the YTD New Hire measure correctly sums the headcount is likely due to the filtering conditions in your DAX formula. Since the formulas are structured identically except for the filter condition ("New" vs. "Term"), the root cause is likely data-related.

One possible reason is that there are no records in Master_In_Out where New/Term = "Term". To verify this, you can create a table visual in Power BI with Master_In_Out[New/Term] and Master_In_Out[Headcount] to check whether "Term" values exist in the dataset. If the dataset has variations like "term" or "TERM", the case-sensitive comparison in DAX might be failing. To avoid this, modify the filter condition using LOWER() to ensure the comparison works regardless of case.

YTD_Exit =
CALCULATE(
    SUM(Master_In_Out[Headcount]),
    FILTER(
        Master_In_Out,
        LOWER(Master_In_Out[New/Term]) = "term"
    ),
    FILTER(
        Master_In_Out,
        SELECTEDVALUE(Master_In_Out[Period_Year])
    )
)

Another possible reason is that the Headcount column contains BLANK() values, which would cause SUM() to return BLANK(). Instead of SUM(), using SUMX() ensures that non-blank values are included in the calculation.

YTD_Exit =
CALCULATE(
    SUMX(Master_In_Out, Master_In_Out[Headcount]),
    FILTER(Master_In_Out, Master_In_Out[New/Term] = "Term"),
    FILTER(Master_In_Out, SELECTEDVALUE(Master_In_Out[Period_Year]))
)

Additionally, there may be an issue with SELECTEDVALUE(Master_In_Out[Period_Year]) returning BLANK(). If no year is explicitly selected, this filter will eliminate all rows. To test whether Period_Year is correctly selected, create a test measure.

Test Period Year = SELECTEDVALUE(Master_In_Out[Period_Year])

If this measure returns BLANK(), it means that no value is currently selected for Period_Year. A better approach would be to provide a fallback value using COALESCE(), which defaults to the latest available year in the dataset when no selection is made.

YTD_Exit =
CALCULATE(
    SUMX(Master_In_Out, Master_In_Out[Headcount]),
    FILTER(Master_In_Out, Master_In_Out[New/Term] = "Term"),
    FILTER(Master_In_Out, Master_In_Out[Period_Year] = COALESCE(SELECTEDVALUE(Master_In_Out[Period_Year]), MAXX(ALL(Master_In_Out), Master_In_Out[Period_Year])))
)

If the issue persists, you may need to check if any external report filters or slicers are affecting the calculation. To debug further, try removing all filters from Master_In_Out using ALL().

Best regards,