Using rankx with stacked columsn

[pmdci]

So I got a measured based on RANK() to work as a filter for a column chart.

It works quite well. I can specify values to be less than or equal 10 in order to see my top 10 values.

However, things get all messy if I then try to split the colums by using another categorical variable as legend (i.e. to stack the columns). As soon as I add a field as a legend, my TOP 10 chart which was working fine so far ends up with way more than 10 columns.

Any ideas what I am doing wrong?

Thanks in advance for your help and support.

Regards,

P.

[Eric_Zhang]

Hi pmdci,

What do you mean "ends up with way more than 10 columns", can you post a snapshot and the measure DAX?

It would be even better if you can upload a pbix with sample data that can reproduce your problem.

[pmdci]

Here is what I mean in pictures.

Without using a legendUsing a legend

As you can see, my rankx() function works perfectly when I don't have a legend field in my column visual. I always get 10 columns, excelpt if I have a tie among ranked values, which is quite rare.

Now if I add a categorical field as my legend in order to make it a stacked column visual, the logic of ranx() seems to break.

I cannot upload this report because is a direct query to an SSAS server.

Regards,

P.

[pmdci]

Hi there,

I am having some issues with the RANKX function in order to show TOP 10 facts, which is driving me around the bend. What puzzles me the most is that I already created one RANKX measure that works perfectly. All that I did is to make a copy of the one that works and try to change one parameter to point to another field.

So I have a fact table called CASES that has all of our support incidents. In this table I have a bunch of measures that I use to count incidents based on given filters. There are two in particular:

1. Overall count of cases - A simple COUNTROW() with no filters.
2. Count of Resolved cases - A COUNTROW() with a filter to look only for cases with the status of "RESOLVED".

I created on RANKX() in my CASES table that does the following:

• Subject Rank by Count of Cases:=RANKX(ALLSELECTED('Support Subjects'[Subject]), [Overall Count of Cases])

Basically what I am doing with this rank is finding out the top Support Subjects based on the count of cases. This rank is then used in Power BI as a FILTER to a visual, where I say to only show those where Rank is less than or equal to 10. This way I show the top 10 subjects for my RESOLVED CASES.

Note that even though my visual only show the count of RESOLVED CASES, my rank uses the OVERALL COUNT OF CASES. Basically I am trying to make a one-size-fits-all rank and based on my trial and error, using the topmost count seems to do the trick, since the measure used as the value in the visual (count of resolved cases) will filter this down anyway

(PLEASE CORRECT ME IF I AM WRONG - BUT THE OBSERVED BEHAVIOUR SO FAR SEEMS TO INDICATE THAT I AM RIGHT).

Now here is the second Rank measure that I tried to create, which I am having issues with:

• Call Logger Rank by Count of Cases:=RANKX(ALLSELECTED('Customer Contacts'[Contact Name]), [Overall Count of Cases])

This rank is very similar to the previous one, but this time I am trying the top call loggers. However this simply does not work. When I try adding this measure as a filter and I set it to show records with less than or equal to 10, nothing changes.

I am having a fit here... No idea of what I am doing wrong! Could someone please advise?

Thanks in advance for your help.

Regards,

P.