Top/Bottom N together and drill down

[Anonymous]

Hi, 

I know there are lots of similar topics here, however I'm still struggling with my case. 

I need to show top/bottom N in one matrix which can be drilled down/up. 

My slicer works when I'm on the lowest level, however going up leads to issues (see screenshots below). 

I decided to simplify here a bit, and I'm only using top RANKX to illustrate them. 

My measure for lowest level 5 is: 

Level 5 = IF(ISFILTERED(TABLE[LEVEL 5]) && NOT(ISFILITERED(TABLE[LEVEL 4])), RANKX(ALL(TABLE[LEVEL 5]), [MEASURE], , DESC, DENSE), "fff")

My measure for level 4 is 

Level 4 = IF(ISFILTERED(TABLE[LEVEL 4]) && ISFILTERED(TABLE[LEVEL 5]), RANKX(ALL(TABLE[LEVEL 4]), [MEASURE], , DESC, DENSE), "fff") 

What I want is to show on level 4 Top N (here 5) like it does on level 5. 

Perhaps, I'm not using proper filtering or proper way of RANKX, but it seems that it worked here, so I'm really confused. 

[Greg_Deckler]

Sample data as text that can be copied would help to recreate and troubleshoot this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler 

Thanks for checking this. 

Unfortunately, I'm not sure if I can share here anything. 

But I want to add, that I switched to ISINSCOPE (instead of ISFILTERED), which I found more useful in my case, since I have some page level filters. And I noticed that my measure works (ranks properly) if a lower level is removed from rows. 

Unfortunately, as soon as I add level 5 to Rows, on level 4 it breaks as in my previous post, though works on level 5. 

Top Rank2 = 

if(isinscope(table[level 4]) && not(isinscope[table[level 5])), 
     rankx(all(table[level 4]), [measure], , desc,dense), 

            if(isinscope(table[level 5], rankx(all(table[level 5], [measure], , desc, dense),
                    blank()) 

I'm really concerned that even in this simplified way it's not working. 

[Icey]

Hi @Anonymous ,

How about replacing "ALL" with "ALLSELECTED" in your measures?

Best Regards,

Icey

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

[Anonymous]

Hi @Icey ,

Yes, I tried both ALL and ALLSELECTED, same issue.