Forum Discussion

decarsul's avatar
decarsul
Helper V
5 years ago
Solved

Rank rows by 'unique' id

Good day all,   I'm back with another question, related to my last one. Working on a set of data, where i want to rank rows based on 2 columns.   1st column has a recurring value, which is an id...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, decarsul 

     

    Based on your description, I modified the table as below.

    Table:

     

    You may modified the measure as below. SUM('Table'[WorkorderID]) need to be changed as MAX('Table'[WorkorderID]) because text column can not be used in sum aggregation.

    Rank Measure = 
RANKX(
    FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
    ),
    CALCULATE(MAX('Table'[WorkorderID])),
    ,ASC
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, decarsul 

 

Based on your description, I modified the table as below.

Table:

 

You may modified the measure as below. SUM('Table'[WorkorderID]) need to be changed as MAX('Table'[WorkorderID]) because text column can not be used in sum aggregation.

Rank Measure = 
RANKX(
    FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
    ),
    CALCULATE(MAX('Table'[WorkorderID])),
    ,ASC
)

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

decarsul's avatar
decarsul
Helper V
5 years ago

Thank you very much for the explenation. i'm using this as the basis of my new report!

I have however added an addition to the if statement in the end, that also if the start date is empty to also show 0.