Forum Discussion

Gianpie's avatar
Gianpie
New Member
3 months ago
Solved

count distinct

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 ...
Need Help
  • DanieleUgoCopp's avatar
    DanieleUgoCopp
    3 months ago

    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

DanieleUgoCopp's avatar
DanieleUgoCopp
Super User
3 months ago

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