Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Date sequence validation for duplicates

Hello everyone

I appreciate if you can help me with this consultation. I have the following table:

eduvale011_0-1679697267924.png

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:

eduvale011_1-1679699295218.png

I hope you can help me.

Thank you so much!

8 REPLIES 8
Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhmPkQZ6EPjMyKyuS?e=iVbqzL

Screen Capture #751.pngScreen Capture #752.pngScreen Capture #753.png

Thanks for the reply. On the result, this OK, however I have left another condition: - there may be records with cancellation date but that does not have duplicate the order. in this case, it should also be OK. Please your help. Thank you.

show me an example so I can understand you

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!

InvoiceStateOrderIssue dateCancellation dateResult
235-2023-000446Regularized 00017284403030/01/2023 21:25:43
235-2023-000095LEGAJADA 001728439606/01/2023 22:50:156/01/2023 23:16:37OK
235-2023-000027LEGAJADA 0144943684703/01/2023 18:29:023/01/2023 21:53:35OK
235-2023-000026LEGAJADA 0144943685143/01/2023 18:29:024/01/2023 18:24:54Error
235-2023-000046 0144943685144/01/2023 18:23:30
235-2023-000185LEGAJADA 01449436901513/01/2023 00:19:0313/01/2023 00:22:56OK
235-2023-000201 01449436901513/01/2023 19:39:18
235-2023-000186LEGAJADA 01449436902613/01/2023 00:19:0313/01/2023 00:22:57OK
235-2023-000202 01449436902613/01/2023 19:39:18
235-2023-000200 01449436904113/01/2023 19:39:18
235-2023-000249LEGAJADA 01449436917017/01/2023 18:46:5319/01/2023 11:22:15Error
235-2023-000271 01449436917018/01/2023 18:30:04
235-2023-000248 01449436918117/01/2023 18:46:53
235-2023-000247LEGAJADA 01449436921417/01/2023 18:46:5318/01/2023 11:22:14OK
235-2023-000268 01449436921418/01/2023 18:30:04
235-2023-000437 01449437730130/01/2023 19:20:42
235-2023-000436LEGAJADA 01449437731230/01/2023 19:20:4230/01/2023 20:54:04OK
235-2023-000438LEGAJADA 01449437732330/01/2023 19:20:4231/01/2023 12:18:38OK
235-2023-000440LEGAJADA 01449437733430/01/2023 19:20:4231/01/2023 12:18:39OK
235-2023-000442 01449437738230/01/2023 19:20:42
235-2023-000439 01449437740430/01/2023 19:20:42
235-2023-000441 01449437741530/01/2023 19:20:42
235-2023-000118LEGAJADA 0996122205529/01/2023 00:00:009/01/2023 22:50:58OK
235-2023-000119 0996122205529/01/2023 22:55:49
235-2023-000138LEGAJADA 09961222056310/01/2023 22:21:5811/01/2023 13:23:23OK
235-2023-000157 09961222056311/01/2023 22:47:01

Hello, please confirm if you could review it? Thank you!

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hello, thanks for the clarification.

I attach the requested detail:

InvoiceOrderStateIssue dateCancellation dateResult
235-2023-000478000173751473LEGAJADA01/02/2023 19:23:2201/02/2023 19:27:02ok
235-2023-000481000173751473Regularized01/02/2023 22:27:27
235-2023-000026014494368514LEGAJADA03/01/2023 18:29:0204/01/2023 18:24:54Error
235-2023-000046014494368514Regularized04/01/2023 18:23:30
235-2023-000176014494368993Regularized12/01/2023 18:47:41
235-2023-000181014494368993Regularized12/01/2023 18:47:41
235-2023-000185014494369015LEGAJADA13/01/2023 00:19:0313/01/2023 00:22:56Ok
235-2023-000201014494369015Regularized13/01/2023 19:39:18
235-2023-000186014494369026LEGAJADA13/01/2023 00:19:0313/01/2023 00:22:57Ok
235-2023-000202014494369026Regularized13/01/2023 19:39:18
235-2023-000204014494369030Regularized13/01/2023 19:39:18
235-2023-000249014494369170LEGAJADA17/01/2023 18:46:5319/01/2023 11:22:15Error
235-2023-000271014494369170Regularized18/01/2023 18:30:04
235-2023-000247014494369214LEGAJADA17/01/2023 18:46:5318/01/2023 11:22:14Ok
235-2023-000268014494369214Regularized18/01/2023 18:30:04
235-2023-000438014494377323LEGAJADA30/01/2023 19:20:4231/01/2023 12:18:38Ok
235-2023-000466014494377323Regularized31/01/2023 23:03:30
235-2023-000821014494394521LEGAJADA21/01/2023 18:38:2322/02/2023 16:16:00Ok
235-2023-000840014494394521Regularized22/02/2023 17:12:41

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.

Thanks again.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.