Forum Discussion

UNICODE's avatar
UNICODE
Icon for Helper I rankHelper I
3 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 Orde...
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

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
3 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
Icon for Helper I rankHelper 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
    Icon for Super User rankSuper 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

    • UNICODE's avatar
      UNICODE
      Icon for Helper I rankHelper I
      2 years ago

      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.