Extract data from a table iterating from 2 columns

Question

Hi community,I'm requiring your valuable support to build the script to extract data from table "test_lookup" which contains this information:test_idref_codebench_idresultstart_datetimeend_datetimeTest_1code_ABVS-001PASS11/05/2023 08:40:2111/05/2023 08:49:55Test_2code_ABVS-001PASS20/06/202318:40:0920/06/2023 18:49:43Test_3code_ABVS-001PASS&nbsp;25/11/2023 09:37:2325/11/2023 09:46:57Test_4code_ABVS-001PASS26/11/2023 16:17:4326/11/2023 16:27:17Test_5code_ABVS-001PASS15/04/2024 15:50:1115/04/2024 15:59:45Test_6code_ABVS-002PASS04/03/2023 12:27:0404/03/2023 12:36:38Test_7code_ABVS-002PASS13/03/2023 00:28:1613/03/2023 00:37:50Test_8code_ABVS-002PASS19/06/2023 09:39:0219/06/2023 09:48:36Test_9code_ABVS-002FAIL15/02/2024 11:00:3515/02/2024 11:07:38Test_10code_ABVS-003PASS19/04/2023 10:03:4319/04/2023 10:13:17Test_11code_ABVS-003PASS01/10/2023 07:19:0301/10/2023 07:28:37Test_12code_ABVS-004FAIL30/08/2023 01:31:3330/08/2023 01:40:57Test_13code_ABVS-004PASS15/10/2023 23:18:0515/10/2023 23:27:39Test_14code_ABVS-004PASS06/04/2024 23:32:5206/04/2024 23:42:26Test_15code_ABVS-005PASS07/05/2023 19:36:3107/05/2023 19:46:05Test_16code_ABVS-005FAIL29/08/2023 05:08:0129/08/2023 05:15:04Test_17code_ABVS-005PASS08/11/2023 21:46:5108/11/2023 21:56:25Test_18code_ABVS-005PASS29/11/2023 19:05:5529/11/2023 19:15:29Test_19code_BBVS-001FAIL12/04/2024 05:50:0612/04/2024 05:57:09Test_20code_BBVS-002PASS08/09/2023 01:21:2608/09/2023 01:31:00Test_21code_BBVS-002PASS14/10/2023 02:23:3414/10/2023 02:33:08Test_22code_BBVS-003PASS07/02/2024 10:07:4707/02/2024 10:17:21Test_23code_BBVS-004PASS14/03/2023 08:38:5914/03/2023 08:48:33Test_24code_BBVS-004PASS02/01/2024 19:48:1902/01/2024 19:57:53Test_25code_BBVS-005FAIL17/06/2023 21:06:4917/06/2023 21:15:29Test_26code_BBVS-005PASS04/04/2024 01:53:5204/04/2024 02:03:26&nbsp;I need to extract for every "rfid_code" the 3 "test_id"s with the most recent date.&nbsp; Expected result as follow:rfid_codetest_idstart_datetimecode_ATest_515/04/2024 15:50:11code_ATest_1406/04/2024 23:32:52code_ATest_915/02/2024 11:00:35code_BTest_1912/04/2024 05:50:06code_BTest_2604/04/2024 01:53:52code_BTest_2207/02/2024 10:07:47&nbsp;The idea is to use it as filter in different visuals.&nbsp;Thanks in advance for your valuable support.&nbsp;Kind regards,WGAP47573

ryan_mayu · Accepted Answer

pls try this

_createdate = max('Table'[start_datetime])

Measure = rankx(FILTER(all('Table'),'Table'[ref_code]=max('Table'[ref_code])),[_createdate],,DESC)
&nbsp;
and apply measure to the table visual

&nbsp;
pls see the attachment below

Forum Discussion

Extract data from a table iterating from 2 columns

1 Reply

Related content

Extracting complicated text from a column

Extract Data from sharepoint

Replacing values with Iterations

iterator

Create New Column with Iterating Value

test_id	ref_code	bench_id	result	start_datetime	end_datetime
Test_1	code_A	BVS-001	PASS	11/05/2023 08:40:21	11/05/2023 08:49:55
Test_2	code_A	BVS-001	PASS	20/06/2023 18:40:09	20/06/2023 18:49:43
Test_3	code_A	BVS-001	PASS	25/11/2023 09:37:23	25/11/2023 09:46:57
Test_4	code_A	BVS-001	PASS	26/11/2023 16:17:43	26/11/2023 16:27:17
Test_5	code_A	BVS-001	PASS	15/04/2024 15:50:11	15/04/2024 15:59:45
Test_6	code_A	BVS-002	PASS	04/03/2023 12:27:04	04/03/2023 12:36:38
Test_7	code_A	BVS-002	PASS	13/03/2023 00:28:16	13/03/2023 00:37:50
Test_8	code_A	BVS-002	PASS	19/06/2023 09:39:02	19/06/2023 09:48:36
Test_9	code_A	BVS-002	FAIL	15/02/2024 11:00:35	15/02/2024 11:07:38
Test_10	code_A	BVS-003	PASS	19/04/2023 10:03:43	19/04/2023 10:13:17
Test_11	code_A	BVS-003	PASS	01/10/2023 07:19:03	01/10/2023 07:28:37
Test_12	code_A	BVS-004	FAIL	30/08/2023 01:31:33	30/08/2023 01:40:57
Test_13	code_A	BVS-004	PASS	15/10/2023 23:18:05	15/10/2023 23:27:39
Test_14	code_A	BVS-004	PASS	06/04/2024 23:32:52	06/04/2024 23:42:26
Test_15	code_A	BVS-005	PASS	07/05/2023 19:36:31	07/05/2023 19:46:05
Test_16	code_A	BVS-005	FAIL	29/08/2023 05:08:01	29/08/2023 05:15:04
Test_17	code_A	BVS-005	PASS	08/11/2023 21:46:51	08/11/2023 21:56:25
Test_18	code_A	BVS-005	PASS	29/11/2023 19:05:55	29/11/2023 19:15:29
Test_19	code_B	BVS-001	FAIL	12/04/2024 05:50:06	12/04/2024 05:57:09
Test_20	code_B	BVS-002	PASS	08/09/2023 01:21:26	08/09/2023 01:31:00
Test_21	code_B	BVS-002	PASS	14/10/2023 02:23:34	14/10/2023 02:33:08
Test_22	code_B	BVS-003	PASS	07/02/2024 10:07:47	07/02/2024 10:17:21
Test_23	code_B	BVS-004	PASS	14/03/2023 08:38:59	14/03/2023 08:48:33
Test_24	code_B	BVS-004	PASS	02/01/2024 19:48:19	02/01/2024 19:57:53
Test_25	code_B	BVS-005	FAIL	17/06/2023 21:06:49	17/06/2023 21:15:29
Test_26	code_B	BVS-005	PASS	04/04/2024 01:53:52	04/04/2024 02:03:26

rfid_code	test_id	start_datetime
code_A	Test_5	15/04/2024 15:50:11
code_A	Test_14	06/04/2024 23:32:52
code_A	Test_9	15/02/2024 11:00:35
code_B	Test_19	12/04/2024 05:50:06
code_B	Test_26	04/04/2024 01:53:52
code_B	Test_22	07/02/2024 10:07:47