Forum Discussion

Silvard's avatar
Silvard
Resolver I
1 year ago

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

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

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

    • Silvard's avatar
      Silvard
      Resolver I
      1 year ago

      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?