Get rank position for selected value in slicer

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
3 years ago
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
Podcast test.pbix51 KB

11 Replies

FreemanZ
Super User
3 years ago
try this
Listeners Rank = RANKX(ALL( Podcast[Podcast]), [Listeners],,,Dense)
v-yueyunzh-msft
Community Support
3 years ago
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
1201rankx.pbix29 KB
- meg222
  Frequent Visitor
  3 years ago
  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
    Community Support
    3 years ago
    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
    1201rankx.pbix32 KB