Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SOLVED: RANKX if more than column value

Hi,

 

I want to rank campaigns based on their clickthrough rate but I would only want to rank those with total emails delivered of over 200. 

 

Here's my DAX: 

 

 

Rank Campaign by CTR = 
    IF(
        [Total Emails Delivered]>=200,
        RANKX(ALLSELECTED('Email Analysis'[Campaign Name]),[Clickthrough Rate],,DESC),BLANK())

 

 

 

But in my table, it's seems to start the rank at 2 or 3:

 

Any way to fix this?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Rico,

     

    Thanks for this.

     

    I've managed to solve the issue with this measure:

     

        IF([Total Emails Delivered] >= 200,
            RANKX(FILTER(
                ALLSELECTED('Campaign and Group Slicer'[Campaign Name]),[Total Emails Delivered]>=200),[Clickthrough Rate]))

     

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous Maybe:

    Rank Campaign by CTR = 
        RANKX(ALLSELECTED('Email Analysis'[Campaign Name]),[Total Emails Delivered]>=200,[Clickthrough Rate],DESC)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Ashish_Mathur,

       

      Unfortunately due to my company's policy, I'm unable to share the files with you.

       

      Is there a workaround where you can still help me?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Try this measure.

    Rank Campaign by CTR =
IF (
    HASONEVALUE ( 'Email Analysis'[Campaign Name] ),
    IF (
        [Total Emails Delivered] > 200,
        RANKX (
            FILTER (
                ALL ( 'Email Analysis' ),
                'Email Analysis'[Email Group] = MAX ( 'Email Analysis'[Email Group] )
                    && [Total Emails Delivered] > 200
            ),
            [Click through.Rate],
            ,
            DESC
        ),
        BLANK ()
    ),
    BLANK ()
)

    My result in my sample is as below.

    Best Regards,
    Rico Zhou

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Rico,

       

      Thanks for this.

       

      I've managed to solve the issue with this measure:

       

          IF([Total Emails Delivered] >= 200,
            RANKX(FILTER(
                ALLSELECTED('Campaign and Group Slicer'[Campaign Name]),[Total Emails Delivered]>=200),[Clickthrough Rate]))