Forum Discussion
Using inscope and filtering rows
I'm trying to create a measure that can be used in a graph (x-axis) where the y-axis consists of three levels of hierarchy. At the two top levels I'd like to use distinctcount for JobID (key) for...
Silvard , Try using below measure
DistinctCountByHierarchy =
VAR IsAtLowestLevel = ISINSCOPE('Table'[HierarchyColumn])
VAR TargetHierarchyValues = {10, 11, 12, 13, 14, 15}
VAR IsTargetHierarchyInScope = IF(
IsAtLowestLevel,
COUNTROWS(
FILTER(
ALL('Table'),
'Table'[HierarchyColumn] IN TargetHierarchyValues
)
) > 0,
FALSE
)
RETURN
IF(
IsAtLowestLevel,
IF(
IsTargetHierarchyInScope,
CALCULATE(
DISTINCTCOUNT('Table'[ApplicationID]),
'Table'[HierarchyColumn] IN TargetHierarchyValues
),
DISTINCTCOUNT('Table'[JobID])
),
DISTINCTCOUNT('Table'[JobID])
)
Hi bhanu_gautam
Thanks so much for asssiting but unfortunately it produces the same result.
have you got any other suggestions or perhaps we need to be using another method?