Ranking with duplicated data

[UNICODE]

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!! 

[TomMartens]

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]

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 · Accepted solution]

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

[UNICODE]

I missed the fact that the first solution was a calculated column.   
Thanks for providing the measure code also, they both work!!  

Now I can use this ranking column to filter the visual to always plot a part from within a job order.