count distinct

Hi,

As per our understanding your issue, the reason your refactored measure returns blanks is due to how filter context and variables behave inside CALCULATE() in Microsoft Power BI.

Your original measure works because:

FILTER(Dim_Items, ...)

creates a row context over Dim_Items.

But in your optimized version:

VAR ProgressVal = [% Progress Ratio]

the measure is evaluated only once in the outer filter context, not per row of Dim_Items.

So this condition:

ProgressVal > 0.9

becomes a single scalar evaluation instead of a row-by-row filter.

That’s why you get blanks or incorrect results.

Correct Optimization Pattern

You should keep the row-wise filtering inside FILTER() while still using variables for reusable scalar values.

Try this:

Count_Items_To_Process_v2 =
VAR LimitDate =
    [CurrentDateMinus1]

RETURN
CALCULATE(
    DISTINCTCOUNT(Activity_Log[ItemID]),
    FILTER(
        Dim_Items,
        Dim_Items[Status] IN {"Approved","Started"} &&
        Dim_Items[GoLiveDate] < LimitDate &&
        [% Progress Ratio] > 0.9
    )
)

Why this works

Inside:

FILTER(Dim_Items, ...)

Power BI evaluates:

• each row of Dim_Items
• along with context transition from the measure

So:

[% Progress Ratio]

is recalculated correctly per row context.

Important Concept

This is the key difference:

Performance Optimization Tip

If [% Progress Ratio] is expensive:

• consider materializing part of the logic as a calculated column
• OR pre-aggregating in Power Query/model

because measures inside FILTER() over large dimensions can still be costly.

You can also write:

Count_Items_To_Process_v2 =
VAR LimitDate =
    [CurrentDateMinus1]

RETURN
CALCULATE(
    DISTINCTCOUNT(Activity_Log[ItemID]),
    KEEPFILTERS(
        FILTER(
            Dim_Items,
            Dim_Items[Status] IN {"Approved","Started"} &&
            Dim_Items[GoLiveDate] < LimitDate &&
            [% Progress Ratio] > 0.9
        )
    )
)

This preserves existing filters more safely.

Hope this helps.

Thanks!