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,
Seeking your help to solve the below problem.
Requirment is simple, in a drill down I want to display the count of people who are directly reporting into me. These levels will be displayed in the Matrix visualization and should stay unfiltered even after being drilldown to the last level.
Solved! Go to Solution.
Hi @Anonymous
Create measures
count_level1 = CALCULATE(DISTINCTCOUNT(Sheet2[level2]),ALLEXCEPT(Sheet2,Sheet2[level1])) count_level2 = CALCULATE(DISTINCTCOUNT(Sheet2[level3]),ALLEXCEPT(Sheet2,Sheet2[level1],Sheet2[level2])) count_level3 = IF(ISBLANK(MAX([level4])),0,CALCULATE(DISTINCTCOUNT(Sheet2[level4]),ALLEXCEPT(Sheet2,Sheet2[level1],Sheet2[level2],Sheet2[level3]))) Measure = IF(HASONEFILTER(Sheet2[level4]),0,IF(HASONEFILTER(Sheet2[level3]),[count_level3],IF(HASONEFILTER(Sheet2[level2]),[count_level2],IF(HASONEFILTER(Sheet2[level1]),[count_level1],BLANK()))))
Best Regards
Maggie
Hi @Anonymous
Create measures
count_level1 = CALCULATE(DISTINCTCOUNT(Sheet2[level2]),ALLEXCEPT(Sheet2,Sheet2[level1])) count_level2 = CALCULATE(DISTINCTCOUNT(Sheet2[level3]),ALLEXCEPT(Sheet2,Sheet2[level1],Sheet2[level2])) count_level3 = IF(ISBLANK(MAX([level4])),0,CALCULATE(DISTINCTCOUNT(Sheet2[level4]),ALLEXCEPT(Sheet2,Sheet2[level1],Sheet2[level2],Sheet2[level3]))) Measure = IF(HASONEFILTER(Sheet2[level4]),0,IF(HASONEFILTER(Sheet2[level3]),[count_level3],IF(HASONEFILTER(Sheet2[level2]),[count_level2],IF(HASONEFILTER(Sheet2[level1]),[count_level1],BLANK()))))
Best Regards
Maggie
How is your original data containing users and managers structured?
It is in Employee Manager format
Any direction on the implementation please.
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |