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
... View more