Conditional Drilldown or Previous Level Distinct Count

[Anonymous]

Dear Team,

I am trying to build a Span of Control logic and count the number of management that reports to the previous level of management.

I have the next data set with 5 levels of management.

Level 1	Level 2	Level 3	Level 4	Level 5
astoy@test.com	rmahom@test.com	yfedor@test.com	ssia@test.com	jbanan@test.com
tpesh@test.com	rmahom@test.com	yfedor@test.com	ssia@test.com	jbanan@test.com
alivanov@test.com	rmahom@test.com	yfedor@test.com	ssia@test.com	jbanan@test.com
vtuto@test.com	rmahom@test.com	capst@test.com	ssia@test.com	jbanan@test.com
mnikolai@test.com	rmahom@test.com	capst@test.com	ssia@test.com	jbanan@test.com
mvasylch@test.com	rmahom@test.com	dorer@test.com	fredod@test.com	jbanan@test.com
olen@test.com	osili@test.com	dorer@test.com	fredod@test.com	jbanan@test.com
vverbi@test.com	osili@test.com	dorer@test.com	fredod@test.com	jbanan@test.com
sprok@test.com	osili@test.com	dorer@test.com	fredod@test.com	jbanan@test.com
kwiat@test.com	rvolo@test.com	grabs@test.com	fredod@test.com	jbanan@test.com
vbatluk@test.com	rmahom@test.com	grabs@test.com	fredod@test.com	jbanan@test.com
dpota@test.com	osili@test.com	grabs@test.com	fredod@test.com	jbanan@test.com

I must have a matrix that would count the distinct amount of managers that report to the previous level. I would like to receive the expected count like in the picture:

Could you please share any ideas on how it must be calculated?

Very much appreciate your help.

Thank you and have a great day.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Level3 =
CALCULATE(DISTINCTCOUNT('Table'[Level 2]),FILTER(ALL('Table'),'Table'[Level 3]=MAX('Table'[Level 3])))

Flag =
IF (
    ISINSCOPE ( 'Table'[Level 5] )
        && NOT ( ISINSCOPE ( 'Table'[Level 1] ) )
            && NOT ( ISINSCOPE ( 'Table'[Level 2] ) )
                && NOT ( ISINSCOPE ( 'Table'[Level 3] ) ) && NOT ( ISINSCOPE ( 'Table'[Level 4] ) ),
    2,
    IF (
        ISINSCOPE ( 'Table'[Level 4] )
            && NOT ( ISINSCOPE ( 'Table'[Level 1] ) )
                && NOT ( ISINSCOPE ( 'Table'[Level 2] ) ) && NOT ( ISINSCOPE ( 'Table'[Level 3] ) ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Level 4] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Level 5] = MAX ( 'Table'[Level 5] ) )
        ),
        IF (
            ISINSCOPE ( 'Table'[Level 3] )
                && NOT ( ISINSCOPE ( 'Table'[Level 1] ) ) && NOT ( ISINSCOPE ( 'Table'[Level 2] ) ),
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Level 3] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Level 4] = MAX ( 'Table'[Level 4] ) )
            ),
            IF (
                ISINSCOPE ( 'Table'[Level 2] ) && NOT ( ISINSCOPE ( 'Table'[Level 1] ) ),
                MAXX ( ALL ( 'Table' ), [Level3] ),
                IF (
                    ISINSCOPE ( 'Table'[Level 1] ),
                    CALCULATE (
                        DISTINCTCOUNT ( 'Table'[Level 1] ),
                        FILTER ( ALL ( 'Table' ), 'Table'[Level 1] = MAX ( 'Table'[Level 1] ) )
                    ),
                    CALCULATE ( DISTINCTCOUNT ( 'Table'[Level 1] ) )
                )
            )
        )
    )
)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly