RANKX as Measure with multiple levels and keeping filters

[Leasie_7]

Hello, I have a table called 'Resolved' which contains [Business], [Level 1], [Level 2] & [Level 3] along with many other columns. The "Level" columns contain words and I am looking to be able to rank within the business the count of occurance by hierachy of the preceeding columns. I need to make sure that the "Filters on this page" and "Filters on all pages" still dictate the ranking of the data as a different year, month, business unit may be chosen we would want the rank to be based off those selections. 

Below is an example of the outcome I am looking to achieve. I tried to more easily show the separation with the use of colors. Yes, I need a rank for each level as sometimes we do not need to drill down as far.

Level 1 rank based on Business. 

Level 2 rank within Level 1 based on Business

Level 3 rank within Level 2, within Level 1, based on Business

I am new to PowerBI and the rank function in a measure with page filters and based on other columns in a sort of group/hierarchy is confusing to me. I have read many forums on rank, but just cannot seem to understand if/how to apply in a measure form to my needs and have gone through so many not sure it would be helpful to provide simply because I do not understand. Any help is much appreciated and I would love to gain some understanding of the components if someone has a solution. Thanks!!

[amitchandak · Accepted solution]

@Leasie_7 ,Based on what I got

You should switch the rank using isinscope. 

Please remember the top one in the matrix will be is in scope for all columns below 

Switch(True() ,

isinscope(Table[Level3]), [Level 3 rank measure],

isinscope(Table[Level2]), [Level 2 rank measure],

isinscope(Table[Level1]), [Level1 rank measure]

)

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

[Leasie_7]

Very cool and appreciate the help.

Followup though -- When I simply pull in business, level 1, and RANK level 1. It simply shows me the levels in which are utilized by the business the page is filtered for, which is correct and what I'm looking for. I've added the count to more clearly depict what I'm asking.

However, when I use the combined Rank ISINSCOPE measure it shows me levels within the data I am pulling from and does not limit to simply the business selected. How do I get the yellow rows below that do not apply with a count of blank to not appear?

[v-xiaotang]

Hi @Leasie_7 

Thanks for reaching out to us.

>> However, when I use the combined Rank ISINSCOPE measure it shows me levels within the data I am pulling from and does not limit to simply the business selected. How do I get the yellow rows below that do not apply with a count of blank to not appear?

Could you share some sample data and measure code? thanks.

Best Regards,

Community Support Team _Tang

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

[Leasie_7]

I had a relationship setup incorrectly, still a noob. ISINSCOPE worked great!