Forum Discussion
count distinct
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
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
)
)