Forum Discussion

Navaneeth_'s avatar
Navaneeth_
Regular Visitor
3 years ago

RANKX

I have a Email Interactions table which is at Interaction_ID and SDR_NAME(Sales rep name) level. I want to rank them based on a score (A measure), I'm using the below formula, using ALL() for the rank to consider all the SDRs and not just the selected one. This formula works fine, when I select a particular SDR it give the corresponding rank. But the rank becomes 1 when the timeline slicer is used. I tried ALLEXCEPT(Date Column) instead of ALL(), still not getting the right result. I'm new to dax, can I get some help here?

 

Email Rank =
RANKX(
    ALL('SALESLOFT_DETAILS'[SDR_NAME]),
    ([Email_Score]+[Email_Reply_Score])/2,
    ,
    DESC,
    Dense
)

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Navaneeth_ 
    Please try

    Email Rank =
RANKX (
    ALL ( 'SALESLOFT_DETAILS'[SDR_NAME] ),
    CALCULATE ( [Email_Score] + [Email_Reply_Score], ALL ( 'Table'[Date] ) ) / 2,
    ,
    DESC,
    DENSE
)
  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi,

    It is likely that your expression is returning unexpected values during eavulation. So try creating measure  Also ALL is not as good with RANKX as ALLSELECTED so try using it.

    Generally speaking RANKX is pretty forgiving in my mind so the issue is usually in the expression part of the measure.

    Example:
    Here I have 3 different RANX measures:

    Ranking simple = RANKX(ALLSELECTED(Ranking),Ranking[Average metric])

     

    Ranking simple all = RANKX(ALL(Ranking),Ranking[Average metric])

     

    Ranking simple all EID + desc + dense = RANKX(ALL(Ranking[EID]),Ranking[Average metric],,DESC,Dense)

    In a table they look like this:

     

    Only the second measure is behaving weirdly this is due the ALL's interaction with Year + Month Slicer. When all values are  displayed the issue is gone:

     

    So in short, try using ALLSELECTED instead of ALL and double check your expression part of the measure.

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/