Group values based on recent 5 records and show in single row (cone)

[Krutika124]

I have below data

Run	RUNID	Job TerminalState	time
XYZ	1	Failed	Thu, 13 Feb 2025 13:19:05
XYZ	2	Failed	Thu, 13 Feb 2025 13:03:42
XYZ	3	Success	Fri, 07 Feb 2025 07:21:55
XYZ	4	Failed	Fri, 07 Feb 2025 07:07:52
XYZ	5	Failed	Wed, 05 Feb 2025 15:43:51
XYZ	6	Success	Fri, 17 Jan 2025 07:38:35
XYZ	7	Success	Fri, 17 Jan 2025 07:35:55

I need output in below format: 

Run	Job TerminalState
XYZ	Failed Failed Success Failed Failed

[manikumar34]

@Krutika124 ,

Measure =

VAR a=

FILTER(ADDCOLUMNS('Table',"rank",RANKX(ALL('Table'[Run ],'Table'[time]),'Table'[time],,DESC)),[rank]<=5)

RETURN

CONCATENATEX(a,[Job TerminalState],",")

On above screenshot table is filtered to top 5, if you do not want to do that then use the below DAX. 

Measure =

VAR a=

FILTER(ADDCOLUMNS('Table',"rank",RANKX('Table','Table'[time],,DESC)),[rank]<=5)

RETURN

CONCATENATEX(a,[Job TerminalState],",")

[Deku · Accepted solution]

Can add this to a table in your report with Run id to get the last 5 status

measure = 
CONCATENATEX( 
   TOPN( 5, table1, table1[Time], desc )
   , table1[JobTerminalState], " ", table1[Time], desc
)

[Krutika124]

Thanks Thats is working 😊 One more thing, If this status we would like to show with Icon then ?
I have tried but it is showing only one status with icon

[manikumar34]

[Deku]

measure = 
CONCATENATEX( 
   TOPN( 5, table1, table1[Time], desc )
   , SWITCH(
       table1[JobTerminalState],
       "Failed", "❌",
       "Cancelled", "⚫",
       "Succeeded", "✔️"
    ),
    , " ", table1[Time], desc
)

[Krutika124]

Thanks. But not working DAX gives error for below 

" ", table1[Time], desc

I tried removing it but gives no data

[Deku]

I have a extra , on that row at the start, need to remove that

[Krutika124]

Yes removed but no data it gives. 

[Deku]

ah, You might have to find and enter emoji's yourself as the forum converts them into a funny format 

https://emojipedia.org/check-mark

[Krutika124]

Thanks...one last question. If I have to keep only the unique value in status then What I have to give?

[Deku]

measure = 
CONCATENATEX( 
   DISTINCT(
      SELECTCOLUMNS( 
         TOPN( 5, table1, table1[Time], desc )
         ,table1[JobTerminalState]
     )
   )
   , SWITCH(
       table1[JobTerminalState],
       "Failed", "emoji 1",
       "Cancelled", "emoji 2",
       "Succeeded", "emoji 3"
    ),
    " "
)

[Krutika124]

I am using below DAX:

CONCATENATEX(

 distinct(SELECTCOLUMNS(TOPN( 5, Table1, Table1[__time], desc ),"table1",table1[RUNID]))

   ,[table1],","

)

I am getting below output for RUNID but it should contain unique values.

Run	RUN ID	Job TerminalState
XYZ	1,2,1,1	Failed Failed Success Failed Failed

[Krutika124]

I am able to get the output like this. But I need to show only recent run status (like only 5 recent states)
I have used the DAX to get this:

DatabricksVisual = concatenatex(Filter(Table1,[Run]  = earlier(Table1[Run]) ) , [jobTerminalState]," " )

Run	Job TerminalState
XYZ	Failed Failed Success Failed Failed Failed success Failed