Issue with Filtering top N using measure in a treemap hierarchy – possible bug

[sdayalan]

My data contains 3 measures in following hierarchy, Career Area, Sub Occupation, Skill. Every Job posting will be assigned to 1 Career Area, 1 Sub Occupation and multiple Skills. I want to find for every employer the top Career Area, within a Career Area I want to see top 10 Sub Occupations and within a sub Occupation I need top 10 skill. All based on sum of jobs under the same Career Area, Sub occupation, Skill.

I’m direct querying from a table that contains this data.

As Power BI does not allow more than 1 TopN filter in a hierarchy, I decided to create a measure that returns top 10 in every hierarchy. Below is the measure I used. It’s using RANKX function. I tried the same using TopN and that did not work either,

Measure1:

top 10 subocc by ca = CALCULATE ([Sum of Bin_Count],FILTER(VALUES('bi org_level_occfamily_drilldown_analysis'[CareerAreaName]), IF(RANKX(ALL('bi org_level_occfamily_drilldown_analysis'[SubOccName]),[Sum of Bin_Count],,DESC)<=10,[Sum of Bin_Count],BLANK())))

Measure2:

top 10 skill in subocc = CALCULATE ([Sum of Bin_Count],FILTER(VALUES('bi org_level_occfamily_drilldown_analysis'[SubOccName]), IF(RANKX(ALL('bi org_level_occfamily_drilldown_analysis'[SkillName]),[Sum of Bin_Count],,DESC)<=10,[Sum of Bin_Count],BLANK())))

Measure3:

Final pick = SWITCH (TRUE(),

    AND(ISFILTERED ( 'bi org_level_occfamily_drilldown_analysis'[CareerAreaName]),ISFILTERED ( 'bi org_level_occfamily_drilldown_analysis'[SubOccName] )),[top 10 skill in subocc],

              ISFILTERED ( 'bi org_level_occfamily_drilldown_analysis'[CareerAreaName] ) , [top 10 subocc by ca],

    [Sum of Bin_Count]

)

Note: I tried the same with If loop too. In either case it works only for the 1st condition. In measure 3 I’m checking for” [top 10 skill in subocc ]“ and so it works fine for 3rd hierarchy and displays top 10 skills in the selected subocc. When I rearrange and place “[top 10 subocc by ca]” as the first case, it give top 10 sub occupation in selected career area and a treemap of all skills in selected subocc instead of top 10 skills in the selected subocc

Output:

Hierarchy1

When I click on Information Technology Career Area it drills down to Sub Occupation in Information Technology but does not filter to display just top 10

When I click on Software Developer / Engineer Occupation, It Should drill down to top 10 skills in Software Developer / Engineer

[v-qiuyu-msft]

Hi @sdayalan,

Would you please share pbix file so we can test it? Please do remove sensitive data before sharing. 

Best Regards,
Qiuyun Yu

[sdayalan]

 Hi @v-qiuyu-msft Qiuyun Yu & @Vicky_Song,

I have a sample pbix file in below location. Please have a look and let me know if there is a fix or workaround for this issue. Please note that I'm using "direct query" to connect to the data.

https://www.dropbox.com/s/cep8ot6poz1cj5s/TopN%20hierarchy%20Sample.pbix?dl=0

Thanks,

sdayalan