Dynamic count by hierarchy based on drill down

[ezpowerTW]

Hi,

I am creating a model to visualise the results of our engagement survey. Users will be able to drill down the results based on the org structure (four level). The challenge is that, to protect the anonymity of the results, I cannot display the results for teams with less than 5 employees. I have tried to create a filter based on a measure [i.e. calculate(count(employee ID))>5], but this ended up excluding the teams with less than 5 employees even from the overall company level.

In the example below, I need to visualise Level 1 where all 14 employees count; same for Level 2, all employees count; on level 3, I can visualise both Business Unit as the total headcount by Business Unit is greater than 5; however, on Level 4, I cannot show the results for Department 1 and 2 because the headcount of each is less than 5 (the only data point that should show on my visual for Level 4 is Department 3)

Level 1	Level 2	Level 3	Level 4	Employee Count
Company	Division A	Business Unit 1	Department 1	3
Company	Division A	Business Unit 1	Department 2	4
Company	Division A	Business Unit 2	Department 3	7

Again, with the approach I am using now, i.e calculate(count(employee ID))>5, the 3 and 4 employees of Deparment 1 and 2 are excluded from all Levels.

Any help is greatly appreciated.

Thanks

[v-xicai]

Hi @ezpowerTW ,

You may create measure Filter1 like DAX below, then put the Filter1 in the Visual Level Filter of the Matrix visual which displays the four levels, setting Filter1 as "is not blank".

Filter1 =
VAR _Count =
    CALCULATE ( COUNT ( [employee ID] ) )
RETURN
    IF ( _Count > 5, 1, BLANK () )

Best Regards,

Amy 

Community Support Team _ Amy

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

[ezpowerTW]

Hi Amy,

Unless I have misunderstood your instructions, your solution does not work as it is excluding from the level 1 calculations employees who are in a team of less or equal than 5 on level 4.

Thank you again for your time.

[Greg_Deckler]

@ezpowerTW - Not sure I am entirely following this but you should be able to use the concept of a Complex Selector to determine whether or not to include/show certain information. This way you can include it in the calculation but not show it at the lowest granularity. Probably will want to use ISINSCOPE, HASONEVALUE, etc. to find your place in the hierarchy. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[ezpowerTW]

Thank you Greg. I will try your suggestions and provide some feedback once I am done.

Cheers!