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 

 

We limit the scope of the rank by applying that filter in the rankx to ALL(Table). 'customerid= max(customerid)' will filter the rows where customerid equals to the cutomerid in current context in ALL(Table),

 

If the customerid isn't a number but a text. It will have no effect on the 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

Hi Allan,

 

Thank you for getting back to me, and explaining the sorting / filtering. That helps a lot in understanding its basic functionality.

 

I agree that the customer id can be anything, but the workorderid has to be integer right? Because when i change your sample file, remove the integer / number characteristic of the column and add a W infront of the numbers, i nolonger get results.

 

How would i go about that?

 

 

  • 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.