count distinct

[Gianpie]

In Power BI I’m trying to optimize a measure that counts distinct items based on several conditions, but when I refactor it using variables to improve performance, I either get blanks or errors, how can I restructure it properly? measure_old: Count_Items_To_Process = CALCULATE( DISTINCTCOUNT(Activity_Log[ItemID]), FILTER( Dim_Items, Dim_Items[Status] IN {"Approved","Started"} && Dim_Items[GoLiveDate] < [CurrentDateMinus1] && [% Progress Ratio] > 0.9 ) ) measure_new (returns blanks): Count_Items_To_Process_v2 = VAR LimitDate = [CurrentDateMinus1] VAR ProgressVal = [% Progress Ratio] RETURN CALCULATE( DISTINCTCOUNT(Activity_Log[ItemID]), Dim_Items, Dim_Items[Status] IN {"Approved","Started"}, Dim_Items[GoLiveDate] < LimitDate && ProgressVal > 0.9 )

[danextian]

Hi @Gianpie 

As a general rule, it’s better to filter specific columns rather than applying filters at the full table level. If the goal is to preserve existing filter context while refining it, KEEPFILTERS should be used on those column-level conditions.

For measures, it’s also important to define the correct grain of evaluation. This is where VALUES() becomes important- it limits the evaluation context to a single column so the measure is computed at a controlled level of detail rather than across the entire table.

That column should ideally represent the lowest appropriate grain with the least necessary cardinality for the calculation. In other words, it should be a stable grouping column that matches how the measure is intended to behave.

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

[ryan_mayu]

@Gianpie 

you can try this

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

if this does not work, pls provide some sample data and expected output

[SamInogic]

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:

Pattern	Behavior
VAR x = [Measure] outside FILTER	Evaluated once
[Measure] inside FILTER	Evaluated per row

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!

[krishnakanth240]

Hi @Gianpie 

Can you try this 

Count_Items_To_Process =

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)))

[DanieleUgoCopp · Accepted solution]

Hello,
I think you need to keep row context behavior inside CALCULATE, not outside it.

Count_Items_To_Process =
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
)
)

I’m not completely sure, but the key here is keeping FILTER so the measure evaluates per row, otherwise the logic breaks.
Best regards,
Daniele