Use of RANKX() for further calculations

[Anonymous]

Hi all,

I have this table which comes sorted randomly:

Date	Product	Batch	Shift
27-04-2020	Product 1	2N7J	Morning
08-05-2020	Product 1	5R3E	Morning
19-05-2020	Product 2	848Y	Evening
19-05-2020	Product 2	848Y	Evening
19-05-2020	Product 2	8S9G	Evening
22-05-2020	Product 1	8V2U	Evening
22-05-2020	Product 4	8V3T	Night
26-05-2020	Product 1	AX3S	Morning
08-06-2020	Product 1	CL7R	Evening
12-06-2020	Product 1	DR2F	Evening
19-06-2020	Product 1	FF4P	Evening
08-07-2020	Product 1	LD4N	Morning
07-07-2020	Product 3	LL7A	Morning
13-07-2020	Product 1	NH8U	Morning
16-04-2020	Product 1	WS9V	Night
16-04-2020	Product 1	WS9W	Evening

What I need is to rank the bathes by date and shift, going from older to newest which is first comes the newest date and shift is first evening, then morning and oldst is night shift.

I've tried to do it with 

Rank = RANKX(FILTER('Table','Table'[Product]=EARLIER('Table'[Product])),'Table'[Batch],,DESC,Dense) just to rank the dates but it looks like as the table is not sorted by date, the rank is not working properly.

This is what I'd like to get is something like this, in order to pick the last three for further calculations:

Date	Product	Batch	Shift	Rank
27-04-2020	Product 1	2N7J	Morning	9
08-05-2020	Product 1	5R3E	Morning	8
19-05-2020	Product 2	848Y	Evening	1
19-05-2020	Product 2	848Y	Evening	1
19-05-2020	Product 2	8S9G	Evening	2
22-05-2020	Product 1	8V2U	Evening	7
22-05-2020	Product 4	8V3T	Night	1
26-05-2020	Product 1	AX3S	Morning	6
08-06-2020	Product 1	CL7R	Evening	5
12-06-2020	Product 1	DR2F	Evening	4
19-06-2020	Product 1	FF4P	Evening	3
08-07-2020	Product 1	LD4N	Morning	2
07-07-2020	Product 3	LL7A	Morning	1
13-07-2020	Product 1	NH8U	Morning	1
16-04-2020	Product 1	WS9V	Night	10
16-04-2020	Product 1	WS9W	Evening	11

Which, if I sort it by date to see it better:

Date	Product	Batch	Shift	Rank
13-07-2020	Product 1	NH8U	Morning	1
08-07-2020	Product 1	LD4N	Morning	2
07-07-2020	Product 3	LL7A	Morning	1
19-06-2020	Product 1	FF4P	Evening	3
12-06-2020	Product 1	DR2F	Evening	4
08-06-2020	Product 1	CL7R	Evening	5
26-05-2020	Product 1	AX3S	Morning	6
22-05-2020	Product 1	8V2U	Evening	7
22-05-2020	Product 4	8V3T	Night	1
19-05-2020	Product 2	848Y	Evening	1
19-05-2020	Product 2	848Y	Evening	1
19-05-2020	Product 2	8S9G	Evening	2
08-05-2020	Product 1	5R3E	Morning	8
27-04-2020	Product 1	2N7J	Morning	9
16-04-2020	Product 1	WS9V	Night	10
16-04-2020	Product 1	WS9W	Evening	11

Any help, please?

Thanks in advance!

[parry2k]

@Anonymous there is one explaining RANKX blog post that can help you sort ranking for multiple columns. Check it here. 

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

If for some reason, it doesn't help, explain what you did and what is not working and we can look into it.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi again!

Thanks for your help but I've read the guidance but I still get the same problem and I know I'm doing something grong but I don't how how to do ir correctly. At the end, I'm never able to rank it from newest to oldest. And I know I need to put a condition where I filter all the dates before the date in the actual row but also I need to filter the product for that row. And I can't get a place to put both conditions properly.

I've tried many combinations but the most close I get is with:

RANKX(FILTER(ALL('Table'[Batch],'Table'[Date]),'Table'[Date]<TODAY()),'Table'[Batch],,ASC,Dense)

RANKX(FILTER(ALL('Table'[Batch],'Table'[Date]),DATESYTD('Table'[Date])),DISTINCTCOUNT('Table'[Batch]),,ASC,Dense)

But anyways, it looks like it still ranking it as they are in the table at first.

Any help with it, please?

Thanks!

[harshnathani]

Hi @Anonymous ,

Can  you explain the logic a little better.

Maybe I am missing something.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Sure! 🙂

I have four columns to take into account for the rank:

DATE         PRODUCT           BATCH          SHIFT

and I have the efficiency as the fifth column with which I'm going to make the calculations afterwards. The goal is to get the average speed of the last three batches for each product.

The data comes in a random sorting, not sorted by date I mean. So I get:

DATE                       PRODUCT            BATCH           SHIFT

09/02/2020            Product 1                A                Morning

09/02/2020            Product 1                A                Evening

10/02/2020            Product 1                A                Night

10/02/2020            Product 1                B                Morning

06/02/2020            Product 1                C                Morning

05/02/2020            Product 1                D                Night

05/02/2020            Product 2                E               Evening

07/02/2020            Product 2                F               Morning

And so on. So What I'd like to get is to get other to rank it as follows:

DATE                       PRODUCT            BATCH           SHIFT             RANK

09/02/2020            Product 1                A                Morning            2

09/02/2020            Product 1                A                Evening             2

10/02/2020            Product 1                A                Night                 2

10/02/2020            Product 1                B                Morning             1

06/02/2020            Product 1                C                Morning            3

05/02/2020            Product 1                D                Night                4

05/02/2020            Product 1                E               Morning             5

05/02/2020            Product 2                F               Evening               2

07/02/2020            Product 2                G               Morning              1

It would be this logic:

For each produc, rank each batch by date and shift, from newest to oldest, taking into account that the actual timelime is

Date X night --> Date X morning --> Date X evening --> Date X+1 night --> Date X+1 morning --> ... and so on

I hope it makes sense!

Thanks a lot for your help

[harshnathani]

Hi @Anonymous ,

10/02/2020            Product 1                A                Night                 2

is this 10/02/2020 or 09/02/2020

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani · Accepted solution]

Hi @Anonymous ,

Create a COLUMN

_add = 

var a = CALCULATE(MAx('Table'[DATE ]), FILTER('Table', 'Table'[DATE ] = EARLIER('Table'[DATE ]) && 'Table'[ Product] = EARLIER('Table'[ Product]) && 'Table'[Batch] <> EARLIER('Table'[Batch])))

var b = IF (a <> BLANK() && 'Table'[Shift] = "Morning" , 3, 0)

RETURN



SWITCH(
    TRUE(),
   a <> BLANK() && 'Table'[Shift] = "Morning",.3,
   a <> BLANK() && 'Table'[Shift] = "Evening",.2,
   a <> BLANK() && 'Table'[Shift] = "Night",.1
)

Create the Rank Column

Ranki = 
var a = MAXX(FILTER('Table','Table'[Batch] = EARLIER('Table'[Batch]) && 'Table'[ Product] = EARLIER('Table'[ Product])),'Table'[DATE ])

var b = RANKX(FILTER('Table','Table'[ Product] = EARLIER('Table'[ Product])),'Table'[DATE ] + 'Table'[_add] ,,DESC,Dense)
RETURN
b

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

It worked!!!!!

Thank you very much for your help!

[harshnathani]

HI @Anonymous ,

Hapy to help 🙂

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)