I appreciate if you can help me with this consultation. I have the following table:
I need to perform the following validations:
- check if there is duplicate data in the "Order" column since it is with this data that I will work.
- once identified, validate that the number of the "Invoice" field whose "Status" field is "FILED" is less than the second record
- After that, validate that the "Cancellation Date" of the line indicating "FILE" of the duplicate pair is later than the "Date of issue" of the same line and that, in turn, it is prior to the date of issuance of the second record (the one with the invoice number greater than the duplicate pair. consider date and time.
- if this sequence is correct, indicate OK in the "Result" field of the line in which the "Status" field indicates "FILE".
- if incorrect, indicate "Error".
- For orders that are not duplicated, leave the "Result" field blank
Finally, create a calculated measure that counts how many "Errors" there are in the column, without counting blank spaces.
The result would be something like this:
I hope you can help me.
Thank you so much!
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Hello, thank you. Attached example table. In red the records that are with FILE status but that do not have duplicate order, but that should appear as OK.
An additional query, this columa result, can be added to the table and not as a measure? in that case, the other fields that are blank, could be with OK? This would be easier for me to be able to count the errors and show them in a label in the visualizations.
Thanks a lot!
|Invoice||State||Order||Issue date||Cancellation date||Result|
|235-2023-000095||LEGAJADA||00172843960||6/01/2023 22:50:15||6/01/2023 23:16:37||OK|
|235-2023-000027||LEGAJADA||014494368470||3/01/2023 18:29:02||3/01/2023 21:53:35||OK|
|235-2023-000026||LEGAJADA||014494368514||3/01/2023 18:29:02||4/01/2023 18:24:54||Error|
|235-2023-000185||LEGAJADA||014494369015||13/01/2023 00:19:03||13/01/2023 00:22:56||OK|
|235-2023-000186||LEGAJADA||014494369026||13/01/2023 00:19:03||13/01/2023 00:22:57||OK|
|235-2023-000249||LEGAJADA||014494369170||17/01/2023 18:46:53||19/01/2023 11:22:15||Error|
|235-2023-000247||LEGAJADA||014494369214||17/01/2023 18:46:53||18/01/2023 11:22:14||OK|
|235-2023-000436||LEGAJADA||014494377312||30/01/2023 19:20:42||30/01/2023 20:54:04||OK|
|235-2023-000438||LEGAJADA||014494377323||30/01/2023 19:20:42||31/01/2023 12:18:38||OK|
|235-2023-000440||LEGAJADA||014494377334||30/01/2023 19:20:42||31/01/2023 12:18:39||OK|
|235-2023-000118||LEGAJADA||099612220552||9/01/2023 00:00:00||9/01/2023 22:50:58||OK|
|235-2023-000138||LEGAJADA||099612220563||10/01/2023 22:21:58||11/01/2023 13:23:23||OK|
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.
Hello, thanks for the clarification.
I attach the requested detail:
|Invoice||Order||State||Issue date||Cancellation date||Result|
|235-2023-000478||000173751473||LEGAJADA||01/02/2023 19:23:22||01/02/2023 19:27:02||ok|
|235-2023-000026||014494368514||LEGAJADA||03/01/2023 18:29:02||04/01/2023 18:24:54||Error|
|235-2023-000185||014494369015||LEGAJADA||13/01/2023 00:19:03||13/01/2023 00:22:56||Ok|
|235-2023-000186||014494369026||LEGAJADA||13/01/2023 00:19:03||13/01/2023 00:22:57||Ok|
|235-2023-000249||014494369170||LEGAJADA||17/01/2023 18:46:53||19/01/2023 11:22:15||Error|
|235-2023-000247||014494369214||LEGAJADA||17/01/2023 18:46:53||18/01/2023 11:22:14||Ok|
|235-2023-000438||014494377323||LEGAJADA||30/01/2023 19:20:42||31/01/2023 12:18:38||Ok|
|235-2023-000821||014494394521||LEGAJADA||21/01/2023 18:38:23||22/02/2023 16:16:00||Ok|
In addition to this, the calculated measure of how many errors there are. In this case, it should show 2 on the visual card I will place.