Get latest not null value for similar row with condition

[Anonymous]

Hi,

I would like to get the latest not null value of a column with some condition
Here is an example of my database

name	type	execution	result	ticket	date	job_id
aaa	OOBE	Internal	Pass		01/01/2000	1
bbb	OOBE	Internal	Pass		01/01/2000	1
aaa	OOBE	Internal	Fail	A-503	01/02/2000	2
bbb	OOBE	Internal	Pass		01/02/2000	2
...	...	...	...	...	...	..
aaa	OOBE	Internal	Fail		01/05/2000	3
bbb	OOBE	Internal	Fail	A-505	01/05/2000	3
aaa	OOBE	Internal	Pass		01/05/2000	4

I would like if the test is still fail in the following day, to get the latest ticket for the same name, type and execution. 
An exanple of what I expect:

name	type	execution	result	ticket	date	job_id
aaa	OOBE	Internal	Pass		01/01/2000	1
bbb	OOBE	Internal	Pass		01/01/2000	1
aaa	OOBE	Internal	Fail	A-503	01/02/2000	2
bbb	OOBE	Internal	Pass		01/02/2000	2
aaa	OOBE	Internal	Fail	A-503	01/05/2000	3
bbb	OOBE	Internal	Pass	A-505	01/05/2000	3
aaa	OOBE	Internal	Pass		01/05/2000	4

Also, as you can see in the expected result. If the result become "Pass" the ticket should not be added. What it mean for me. Is that I should only get the latest not null value of ticket that was fail. If the latest result was pass. We should not take the ticket in consideration. 

I hope my topic is clear. If anyone need more explanation, do not hesitate !

Best regards

Steph

[v-shex-msft · Accepted solution]

HI @Anonymous ,

Do you mean to summary your records and only show the last records who have a ticket number? If this is a case, you can try to use the following DAX formula to get a summarized table.

Table =
SUMMARIZE (
    FILTER ( Table, [Ticket] <> BLANK () ),
    [job_id],
    [Date],
    [Name],
    [Type],
    [execution],
    "ticket", MAX ( Table[ticket] )
)

Notice: Your ticket column seems like text type, it is hard to find out first/last text value, I'd like to suggest extract numeric value to a new column and apply max function on new column to get last value.

Regards,

Xiaoxin Sheng