Get rank position for selected value in slicer

[meg222]

Hi all,

I have a measure which calculates the rank for a column based on a measure I created called "Listeners" which gives the below results:

Listeners Rank = RANKX(ALLSELECTED( Podcast[Podcast]), [Listeners],,,Dense)

I need to have a slicer with the podcast name and a card (or similar visualisation) which shows what it's rank is. So for example if "BBC Earth Podcast" is selected in the slicer then the card should show "7" as that podcast is ranked 7 out of all the podcasts.

Tried a lot of solutions but not found any which work yet - the main problem I'm having is that the Listeners Rank measure I have just returns 1 when a podcast is selected. 

Thanks in advance

[v-yueyunzh-msft]

Hi , @meg222 

According to your description, you want to have a [Podcast] sliver and return the Rank value in the card visual.

For this need , you can make the rankx() function in the calculated column, then you just put the column you created in the visual.

If you want to create a measure . Here are the steps you can refer to :
(1)This is my test data:

(2)We need to create a table with one column as a slicer :

Table = VALUES('Podcast'[Podcast])

(3)Then we can create  a measure :

Measure 2 = var _t_rank = SUMMARIZE(ALL('Podcast') , 'Podcast'[Podcast] , "RANKX" , RANKX(ALL('Podcast') , CALCULATE( SUM('Podcast'[Listener])),,,Dense))
var _slicer = SELECTEDVALUE('Table'[Podcast])
var _rank = FILTER(_t_rank , [Podcast] = _slicer)
return
MAXX(_rank,[RANKX])

(4)Then we put the 'Table'[Podcast] on the slicer visual and the field we need on the card, then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[meg222]

Hi Aniya,

I couldn't get this method to work for me - there are two issues I'm having:

1. Listeners is a calculated measure (not a column) using the following DAX:

Listeners = DISTINCTCOUNT(Podcast[UNIQUE_ID])

I can't add "Listeners" as a calculated column to the below table because I need it to be dynamic based on other slicer selections as well.

2. The second problem I have is that I need other slicers to work as well as the podcast slicer. The rank will need to be dynamic and change when other slicers are selected. These slicers are stored in the Demographics tables below which is linked by a one-to-one relationship with the Podcast table by the UNIQUE_ID.

So when filtered on gender, for example, I need the rank to change to what is showing in the below matrix. The card for "Off menu" should show 4 when "Female" is selected in the slicer.

Thanks

[v-yueyunzh-msft]

Hi , @meg222 

Thank you for your quick response.

According to your description, the [Listener] is a measure and you want to get the rank value with the slicer filter works. Right?

Here are the steps you can refer to :

(1)This is my test data:

And the table relationship is as below:

(2)I create a measure like you as the [Listener]:

Listener = COUNT('Podcast'[UNIQUE_ID])
And in the table visual , the rank value used is like yours to show the rank:
rankx = RANKX( ALLSELECTED('Demographics'[Podcast]) , [Listener],,,Dense) 

(3)To meet your need , we also need to create a table as a slicer :

Table = VALUES('Demographics'[Podcast])

(4)Then we can create a slicer flag measure to filter the data in the slicer:

Slicer Falg = var _t = VALUES('Demographics'[Podcast])
var _slicer =SELECTEDVALUE('Table'[Podcast])
return
IF(_slicer in _t , 1,0)

We can put this measure on the “Filters on this visual” of the slicer visual like this:

For this , we can filter other slicers and keep the right data in our slicer visual.

(5)Then we can create a measure for the card visual:

Measure = var _t_rank = SUMMARIZE(ALLSELECTED('Demographics') ,'Demographics'[Podcast], "RANKX" , RANKX(ALLSELECTED('Demographics') ,CALCULATE(COUNT('Podcast'[UNIQUE_ID])),,,Dense))
var _slicer = SELECTEDVALUE('Table'[Podcast])
var _rank = FILTER(_t_rank , [Podcast] = _slicer)
return
MAXX(_rank,[RANKX])

(6)In the end , we can put the fields we need on the visual , and we will meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[meg222]

Hi Aniya,

I copied exactly what you did but it still isn't working for me. The rank measure just shows "1" for whichever podcast is selected in the slicer. Also the podcast slicer doesn't seem to work to filter any other visualisations on the page - e.g. a pie chart showing gender doesn't change when the podcast selected in the slicer is changed, it just stays the same showing the gender breakdown for the full data set. Perhaps you can have a look at the attached file and see where I'm going wrong?

https://we.tl/t-4bJH6HJhNx 

[v-yueyunzh-msft]

Hi , @meg222 

Thank you for your quick response and providing the .pbix file to us!

I download the .pbix file and test in my side . The reason is that in your ‘Podcast’ Table has more than one column. So we need to update the [Measure] measure to this:

Measure = var _t_rank = SUMMARIZE(ALLSELECTED('Demographics') ,'Demographics'[Podcast], "RANKX" , RANKX(ALLSELECTED('Demographics') ,CALCULATE(COUNT('Podcast'[UNIQUE_ID]),ALLEXCEPT('Demographics','Demographics'[Podcast])),,,Dense))
var _slicer = SELECTEDVALUE('Table'[Podcast])
var _rank = FILTER(_t_rank , [Podcast] = _slicer)
return
MAXX(_rank,[RANKX])

In the end , we can solve this problem:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[meg222]

Hi Aniya,

I've got the rank card to work now using the podcast filter - thank you!

However I've still got the issue of the other slicers not working in conjunction with the podcast slicer. 

So I need the other visualisations to be filtered by the podcast selection - so the "Count of Gender by Gender" should show the gender breakdown for that podcast - not for the total sample which is what it is currently doing.

Also I need the Rank to update when a gender is selected in the slicer - so if female is selected then the "Measure" rank needs to show what the rank is for females only and match what is showing in the Rankx column in the matrix. Currently the "Measure" is not changing when the gender slicer is applied.

A tab like the below is what I'm trying to create so it will show a summary for the selected podcast. At the moment the Podcast slicer is only working to filter the "Measure" card and the other slicers for age, gender and social grade are only filtering the other visualisations but not the "Measure" card.

Hopefully that makes sense. Thanks so much for all your help on this, I really appreciate it.

[v-yueyunzh-msft]

Hi, @meg222 

Thank you for your quick response ! According to your description, when you filter the [Podcast] filter , the "Count of Gender by Gender" do bot be filtered.

For this reason is that the [Podcast] column is in the new 'Table' we created . And this table dose not make relationship between other tables. And for your first need, you want to select one Podcast and show the rank value in the card visual , it need to create a new table as a slicer.

Now, if you want to keep the filter of the [Podcast], you need to put the 'Demographics'[Podcast] column on a new slicer visual to realize, like this:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[meg222]

Hi Aniya,

Is there no way to have it so the "Measure" card is filtered by the other slicers for the demographics like age and gender? For example in the below screenshot the "Listeners rank" in the matrix shows that among 18-24 year old females this podcast ranks 2nd so I would need the card visual to show 2.

For the podcast slicers - if I have one slicer for 'Demographics'[Podcast] and another slicer for 'Table'[Podcast] is there a way to sync the two slicers so that I can only show one of them and hide the other? I'm just thinking that would be much simpler for the end user to just have to select the podcast in one of them.

Thank you

[v-yueyunzh-msft · Accepted solution]

Hi , @meg222 

Oh.. Thanks for your quick response!The slicer filter did not work. It is my misktake.

You can use this measure to show in the card visual:

Measure2 = 
var _table= SUMMARIZE(ALLSELECTED('Demographics') , 'Demographics'[Podcast] ,"Listener" , [Listener] )
var _t =ADDCOLUMNS( _table ,"rankx", RANKX(_table,[Listener],,,Dense))
var _slicer = SELECTEDVALUE('Table'[Podcast])
var _rank = FILTER(_t , [Podcast] = _slicer)
return
MAXX(_rank,[RANKX])

For the second question, you need to put the 'Table'[Podcast] on the slicer. And for this need , you can not realize it by the oringle slicer in the 'Demographics'.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[meg222]

Hi Aniya,

That works perfectly - thank you!

Also I managed to get the two slicers to show the same podcast by changing the second slicer for 'Table'[Podcast] into a chiclet slicer and enabling "Forced selection" in the settings. The 'Demographics'[Podcast] slicer is single-selection so this works really nicely and I can just hide the chiclet slicer behind another visualisation.

Really appreciate all your time and help on this!

[FreemanZ]

try this

Listeners Rank = RANKX(ALL( Podcast[Podcast]), [Listeners],,,Dense)