get next value in a table group by and having conditions

[cristianj]

Hello, I have the next table :

idclient	id	status	next 3 state id
id1	1	1	3
id1	2	2	3
id1	3	3	12
id1	4	18	12
id2	5	1	7
id2	6	2	7
id2	7	3
id3	8	1	9
id3	9	3
id3	10	2	11
id3	11	3
id1	12	3
id1	13	1
id1	14	18

i want to find a formula to give me the next 3 stateid in which i find the next id for idclient where status=3. Thank you

[johnt75 · Accepted solution]

You could create a calculated column like

next 3 state id =
VAR currentClient = 'Table'[idclient]
VAR currentID = 'Table'[ID]
RETURN
    SELECTCOLUMNS (
        TOPN (
            1,
            FILTER (
                ALL ( 'Table' ),
                'Table'[idclient] = currentClient
                    && 'Table'[status] = 3
                    && 'Table'[id] > currentID
            ),
            'Table'[id], ASC
        ),
        "@val", 'Table'[id]
    )

[cristianj]

Thank you very much!