Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!
Solved! Go to 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 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
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)
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
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)
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)
It worked!!!!!
Thank you very much for your help!
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |