Forum Discussion
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 ...
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
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)))