Forum Discussion

UNICODE's avatar
UNICODE
Helper I
2 years ago
Solved

Ranking with duplicated data

Hello Everyone,  

I have one here that has stumped me.   
I am trying to get a ranking column for the product data table.  I have a slicer on the report that allows the users to choose the Job Order(J/O) and it then pulls up the Part numbers for that J/O.   What I would like is a measure or calculated column that will number the Parts under each J/O (dynamic list)

For example, in the table two measurments are taken for Each Part that is under the J/O.   I would like to create the Rank Column shown in the table.  Note how the Rank resets as you change J/O


I have tried the following measure 

 

 

Test Rank = 
RANKX (
 ALLSELECTED ('TABLE'),
  CALCULATE ( SUM ('TABLE'[INDEX]),
   ,
   ASC,
   DENSE
)

 

 

This almost works but does't give me the same value for the duplicated Part number. 



Thanks!! 

Need Help
  • TomMartens's avatar
    TomMartens
    2 years ago

    Hey UNICODE ,

     

    please be aware that I did not use the DAX to create a measure, instead I created a calculated column.
    If you have to create a measure, try this instead:

    Rank (ms) = 
ROWNUMBER(
    SUMMARIZE(
        ALLSELECTED( 'Table' )
        , 'Table'[J/O]
        ,'Table'[Part]
    )
    , ORDERBY( 'Table'[Part] , ASC )
    , DEFAULT
    , PARTITIONBY( 'Table'[J/O] )
)

    Next,  recommend updating to the latest version of Power BI Desktop because my assumption is that there will be optimizations for the windowing functions like ROWNUMBER.

     

    Hopefully, this provides what you are looking for.

    Regards,

    Tom

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey UNICODE 

    I use the below DAX statement to create a calculated column called RANK:

    Rank = 
ROWNUMBER(
    SUMMARIZE(
        'Table'
        , 'Table'[J/O]
        ,'Table'[Part]
    )
    , ORDERBY( 'Table'[Part] , ASC )
    , DEFAULT
    , PARTITIONBY( 'Table'[J/O] )
)

    The result:

    It looks exactly like the expected result 🙂

    Hopefully, this will help you find what you are looking for.

     

    Regards,

    Tom

    • UNICODE's avatar
      UNICODE
      Helper I
      2 years ago

      Hey Tom Thanks for the reply! 
      Your returned results looks like exactaly what I was trying to accomplish.  
      Building this in Power Bi, I can't seem to get it to work using the June 2023 release.  

      Two things seem to be happening. 
      1. Using the table visual, and selecting don't sum for the Parts the Table only displays single value, where there should be double rows for each part. 

      2. Copy and pasting your code for the rank measue, creates the Rank column but it is always equal to 1 

      I'm trying to figure out how to attached a PBIX file. 

      • TomMartens's avatar
        TomMartens
        Super User
        2 years ago

        Hey UNICODE ,

         

        please be aware that I did not use the DAX to create a measure, instead I created a calculated column.
        If you have to create a measure, try this instead:

        Rank (ms) = 
ROWNUMBER(
    SUMMARIZE(
        ALLSELECTED( 'Table' )
        , 'Table'[J/O]
        ,'Table'[Part]
    )
    , ORDERBY( 'Table'[Part] , ASC )
    , DEFAULT
    , PARTITIONBY( 'Table'[J/O] )
)

        Next,  recommend updating to the latest version of Power BI Desktop because my assumption is that there will be optimizations for the windowing functions like ROWNUMBER.

         

        Hopefully, this provides what you are looking for.

        Regards,

        Tom