Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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.
@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
Thank you Greg. I will try your suggestions and provide some feedback once I am done.
Cheers!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |