Rank rows by 'unique' id

[decarsul]

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 identifier of a customer.

2nd column has a unique value.

Each row has a start and end date.

Now the first step i want to do is rank the rows to the 1st column.

So if i have 10 rows, with 2 unique customer, where the 1st customer has 3 rows, and the 2nd customer has 7 rows, i want the following output:

CustomerId	WorkorderID	Rank
1	1	1
1	2	2
1	3	3
2	12	1
2	13	2
2	14	3
2	15	4
2	16	5
2	17	6
2	18	7

Now ofcourse, this is logical, but in terms of coding it is not. Because i never defined a rank by criteria. So if this is the result it is pure luck.

Now how would i write this in DAX? In OBIEE i would write something like: RANK(WorkorderID BY CustomerID)

I have tried using RankX, but i'm not getting it. So i'm not exactly looking for a answer to my question, i'm more looking for an explanation how to get to that answer.

So the last time i asked this question, i got a full answer. But now i'm hoping someone can explain how i get to that answer.

This is what i eventually want (per customer id):

CustomerId	WorkorderID	Rank	Start date	End date	Within 7 days of previous workorder?
1	1	1	1-1-2019	1-1-2019	0	False, there is no previous workorder
1	2	2	2-1-2019	6-1-2019	1	True, within 1 day a new workorder is created
1	3	3	11-1-2019	15-2-2019	1	True, within 7 days of closing last workorder a new one is created

For this, i've gotten the following code last time. But its not functioning 100% and since i don't understand the elements i can't fix it. (also this one compares start to start, not end old vs start new)

Indicatorx:=VAR thisdate =
MAX ( [Start] )
RETURN
CALCULATE (
COUNT ( [Ticketnr] );
FILTER (
ALLSELECTED ( Table1 );
[UniqueID] = MAX ( [UniqueID])
&& [Start] >= thisdate - 6
&& [Start] <= MAX ( [Start] )
)
)

[craigbarrien1]

This was great and helped me a lot for my scenario. However, when I try to use this in a bar chart to show how much the price is for each of my categories, the values are showing for the overall total not just the ones whose dynamic ranking is 1. Even when I try to use the measure as a filter it shows blank. I know it's been a few years but any help on this would be great. 

[v-alq-msft]

Hi, @decarsul 

Based on your description,  I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below..

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

IsWithin 7 Days = 
var r = [Rank Measure]
var lastenddate = 
MAXX(
    FILTER(
        ALL('Table'),
        [CustomerId]=MAX('Table'[CustomerId])&&
        [Rank Measure]=r-1
    ),
    [End Date]
)
var diff = DATEDIFF(lastenddate,MAX('Table'[Start Date]),DAY)
return
IF(
    ISBLANK(lastenddate),
    0,
    IF(
        diff>=7,
        0,1
    )
)

First, you need to get the value of 'CustomerId' and use it to filter 'All' table.

FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
)

Then you may use 'Rankx' function to get the rank by 'WorkorderID' in the context

 of the above filtered table.

RANKX(
    FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
    ),
    CALCULATE(SUM([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]

Hi Allan,

The results are exactly what i'm looking for, thanks for that.

For me to get this right. 

The Filter in this case acts as my group by statement?

As in, with the filter i select my target group, and by applying that filter in the rankx to ALL(Table) i basically tell it to only do the ranking on each unique customer id that it can find in all columns and rows of the table?

Now how does the customerid= max(customerid) define the uniqueness? i don't understand that.

Also, What if the unique id is not INT but instead varchar. This won't work with a count 😞

[v-alq-msft]

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]

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

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]

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.

[decarsul]

As i don't sit still myself.

I have found the following post: https://community.powerbi.com/t5/Desktop/create-rank-column-based-on-column-using-dax/td-p/567456

This code does roughly what i want. But same as above, i don't understand why what is where?