Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone, I'm trying to check the data of the previous row so I can compare it to the current row and apply some logic. I'm aware of the EARLIER and EARLIEST functions but somehow, it doesn't work as I expected them too. I wrapped them in a FILTER inside a CALCULATE function but it still doen't seem to work.
Below is my sample data:
Order ID | Order Item ID | Qty |
100021 | 0001 | 32 |
100021 | 0002 | 32 |
100036 | 0002 | 10 |
100044 | 0001 | 28 |
100044 | 0002 | 28 |
100044 | 0003 | 28 |
100044 | 0004 | 28 |
100044 | 0005 | 28 |
100100 | 0001 | 49 |
100100 | 0002 | 49 |
100222 | 0003 | 108 |
100223 | 0001 | 87 |
100223 | 0010 | 87 |
100270 | 0001 | 10 |
100272 | 0001 | 16 |
Below is my expected result:
Order ID | Order Item ID | Qty | Calculated Col_Qty |
100021 | 0007 | 32 | 32 |
100021 | 0009 | 32 | |
100036 | 0002 | 10 | 10 |
100044 | 0001 | 28 | 28 |
100044 | 0002 | 28 | |
100044 | 0003 | 28 | |
100044 | 0004 | 28 | |
100044 | 0005 | 28 | |
100100 | 0003 | 49 | 49 |
100100 | 0005 | 49 | |
100222 | 0003 | 108 | 108 |
100223 | 0005 | 87 | 87 |
100223 | 0010 | 87 | |
100270 | 0001 | 10 | 10 |
100272 | 0001 | 16 | 16 |
I can't really depend on the Order Item ID because sometimes, it doesn't start with "0001".
My goal is to check if the current data row of Order ID is the same with the previous row. If it is the same, set the Qty value as blank. If they are not the same, retain the Qty value.
Thank you!
Solved! Go to Solution.
Hi @crln-blue ,
First create an index column and count the number of occurrences of each Order ID.
Index = RANKX(FILTER(ALL('Order'), 'Order'[Order ID] = EARLIER('Order'[Order ID])), 'Order'[Order Item ID],, ASC, Dense)
Create a calculated column that sets the Qty value to blank if index is not equal to 1. If index = 1, Qty value is retained.
Calculated Col_Qty = IF('Order'[Index] = 1,'Order'[Qty],BLANK())
Drag columns to the report page for display.
pbix file is attached.
If you have any further questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi @crln-blue ,
First create an index column and count the number of occurrences of each Order ID.
Index = RANKX(FILTER(ALL('Order'), 'Order'[Order ID] = EARLIER('Order'[Order ID])), 'Order'[Order Item ID],, ASC, Dense)
Create a calculated column that sets the Qty value to blank if index is not equal to 1. If index = 1, Qty value is retained.
Calculated Col_Qty = IF('Order'[Index] = 1,'Order'[Qty],BLANK())
Drag columns to the report page for display.
pbix file is attached.
If you have any further questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
98 | |
89 | |
74 | |
67 | |
62 |