Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
5 years ago
Solved

rankx doesn't work

I have a fact_table called 'opportunity' where score data and close date are stored. The fact table is linked to dim-date table via 'close date' column.

 

I have created a rankx measure to rank scores. The ranking should take into consideration the sliceron the left side. In other words, when I filter close year 2019, I expect the ranking to be updated only among scores within close year 2019. As my opportunity table is linked to dim_date table via [close date] column, I quoted [close date] column in allexcept() formula to make sure close year will be considered in rankx().

 

rank measure = rankx(allexcept(opportunity,opportunity[Status2],opportunity[Probability Group],opportunity[Close Date]),[sum of score])

 

However, this doesn't work. As below, when I select close  year 2019, the highest rank I see is 2. It looks the rankx measure ignores my close year selection on the left.

 

How can I correct the measure so that close year selection is considered? By the way, close year comes from dim_date table.

 

 

  • I changed the measure as below, now it works properly

     

    //rank measure = rankx(allselected(opportunity),[sum of score])

2 Replies

  • Jeanxyz's avatar
    Jeanxyz
    Power Participant

    I changed the measure as below, now it works properly

     

    //rank measure = rankx(allselected(opportunity),[sum of score])
  • Anonymous's avatar
    Anonymous
    Not applicable

    Jeanxyz 


    Indeed, you should use allselected()/all() instead of allexcept() depends on your filtering needs. Glad you solve it.

     

     

    Paul Zheng _ Community Support Team