Forum Discussion

PBIfanatic's avatar
PBIfanatic
Helper V
4 years ago
Solved

Rank without measure.

Hi,

 

Is it possible to rank a column without a measure value?

 

This is what I am trying to achieve.

 

Column 1    Column2   Rank 

Product1      US              1

Product1      UK              2

Product1      AU              3

Product2       Uk             1

Product3       AU              1

 

 

 

 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

     

    Rank CC =
    RANKX (
    SUMMARIZE (
    FILTER ( 'Table', 'Table'[Product] = EARLIER ( 'Table'[Product] ) ),
    'Table'[Country]
    ),
    CALCULATE ( MAX ( 'Table'[Country] ) ),
    ,
    DESC
    )

     

     

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

     

    Rank CC =
    RANKX (
    SUMMARIZE (
    FILTER ( 'Table', 'Table'[Product] = EARLIER ( 'Table'[Product] ) ),
    'Table'[Country]
    ),
    CALCULATE ( MAX ( 'Table'[Country] ) ),
    ,
    DESC
    )

     

     

    • varung8899's avatar
      varung8899
      Helper II
      2 years ago

      I am also getting all the values of Rank as 1 and I think the formula is incorrect. Also how will the formula be if the product column contains integer values instead of Text ? Instead of Pro01 if it has 1. Pls let me know. TIA.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    PBIfanatic  do you need cal.column / measure?

     

    Ranking within each subset with a RANKX measure indeed requires utilizing another measure inside it to my knowledge.

     

     

    _rank = 
//_maxCountry = MAX('Table'[Column2 ])
VAR _0 = MAX('Table'[Column 1 ])
VAR _1 = RANKX(FILTER(ALLSELECTED('Table'),'Table'[Column 1 ]=_0),[_maxCountry],,ASC)
RETURN _1

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      Hi,

      I am not sure if you are using it for a calculated measure or a calculated column.

      As clearly shown in the picture, I created a calculated column.

      You need different formula if you need a calculated measure.

       

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      4 years ago

      Hi,  PBIfanatic 

      Regarding the sample data you provided, I did not find any errors in Jihwan_Kim  formula.

      Please check my attached sample file for more details.

      If your problem hasn't been solved, please share more details or related screenshot for further research.

      Best Regards,
      Community Support Team _ Eason