Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
UNICODE
Frequent Visitor

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

UNICODE_0-1694202742189.png


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

1 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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

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:
image.png
It looks exactly like the expected result 🙂

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.  

UNICODE_0-1694437452922.png

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. 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors