Forum Discussion

Black_magic100's avatar
Black_magic100
Helper III
7 years ago
Solved

How to set RankX values if value = ***

I have a table with 2 columns as seen below.  In this table their are 20 ROWS, which declare a name of a location.  Currently my formula ranks the values accordingly, but I need to adjust the ranking.  How can I add to my formula a condition where if the Sum Column value = Null/Blank then RankX column = 20.

 

Formula:

CycleGiving and Private Rides MTD = CALCULATE(DISTINCTCOUNT(ClassSchedule[ClassScheduleID]),FILTER(ClassSchedule,RELATED('Calendar'[CurMonthOffset] ) = 0), FILTER(ClassSchedule,RELATED('Calendar'[FutureDate] ) = "Past"),FILTER('Classes@ClassCategory','Classes@ClassCategory'[CategoryId] IN {680, 681, 682}),FILTER(ClassSchedule,ClassSchedule[Cancelled] = BLANK()))
 
RankX formula: 
CG/Private Rides Rank =
RANKX (
ALL(GeneralPrefs[LocationName]),
[CycleGiving and Private Rides MTD])

 

Sum Column | RankX Column

6,1

4,2

4,2

3,4

2,5

1,6

1,6

1,6 

1,6

1,6

1,6

1,6

1,6

1,6 

1,6

Blank,16 

Blank,16 

Blank,16 

Blank,16 

Blank,16 

   

               

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Try this measure

     

    RankX formula: CG/Private Rides Rank = IF(ISBLANK([your_measure),20,RANKX(ALL(GeneralPrefs[LocationName]),[CycleGiving and Private Rides MTD]))
     
    your_measure is the measure appearing under the SUM column.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Try this measure

     

    RankX formula: CG/Private Rides Rank = IF(ISBLANK([your_measure),20,RANKX(ALL(GeneralPrefs[LocationName]),[CycleGiving and Private Rides MTD]))
     
    your_measure is the measure appearing under the SUM column.
    • Black_magic100's avatar
      Black_magic100
      Helper III
      7 years ago

      Wow I tried to do the exact same thing, but failed miserably.  I think the hardest part about DAX as I am slowly learning it is realizing how important the order of the syntax is.  Definitely something I will learn over time, but I my mistakes always seem to derive from that.  Stupid mistake for sure.  Thank you friend.