topic Re: Using inscope and filtering rows in DAX Commands and Tips

Using inscope and filtering rows

Silvard — Thu, 12 Sep 2024 08:55:46 GMT

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 all rows, but at the lowest levels, I'd like to use distinctcount for ApplicationID (key) for some rows and for the rest JobID.

I have tried below, but it only seems to be using JobID.

Your help is much appreciated!

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( IsTargetHierarchyInScope, CALCULATE( DISTINCTCOUNT('Table'[ApplicationID]), 'Table'[HierarchyColumn] IN TargetHierarchyValues ), DISTINCTCOUNT('Table'[JobID]) )

Re: Using inscope and filtering rows

bhanu_gautam — Thu, 12 Sep 2024 09:04:23 GMT

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

Re: Using inscope and filtering rows

Silvard — Thu, 12 Sep 2024 09:20:37 GMT

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?