topic Re: Filter Failed jobs in DAX Commands and Tips

Filter Failed jobs

JaimeHernandezP — Wed, 30 Mar 2022 21:58:26 GMT

Hy there I'm so newby with modeling I try to filter failed jobs in my report

I have a table with 3 columns Jobs, Date status.

JOB	DATE	STATUS
app	30/03/2022	Failed
app	30/03/2022	Success
app	31/03/2022	Success

Jobs run daily. If a job fails, it runs again.

I am trying to extract only the failed jobs that have no successful results on the same day.

For example:

JOB	DATE	STATUS	RESULT
app	30/03/2022	failed
app	30/03/2022	success	success
app	31/03/2022	success	success
app	1/04/2022	failed	failed
app	2/04/2022	success	succes

Re: Filter Failed jobs

amitchandak — Thu, 31 Mar 2022 01:57:07 GMT

JaimeHernandezP , Create a measure like this one and plot with Job

Countx(Filter(Table, Table[Job], "_1", countrows(filter(Table, Table[STATUS] ="success")) , "_2", countrows(filter(Table, Table[STATUS] ="failed")))
isblank(_1) && not(Isblank(_2)))

Re: Filter Failed jobs

JaimeHernandezP — Thu, 31 Mar 2022 05:27:58 GMT

Sorry. I tried but the syntax is wrong.

Re: Filter Failed jobs

amitchandak — Thu, 31 Mar 2022 08:57:24 GMT

JaimeHernandezP , Sorry my mistake

Try like

Countx(Filter(Summarize(Table, Table[Job], "_1", countrows(filter(Table, Table[STATUS] ="success")) , "_2", countrows(filter(Table, Table[STATUS] ="failed"))) ,isblank(_1) && not(Isblank(_2)) ) ,[JOB])

Re: Filter Failed jobs

JaimeHernandezP — Thu, 31 Mar 2022 17:34:42 GMT

amitchandak tank you so much for stepping.

I tried your command, but the error persists.

Re: Filter Failed jobs

v-kkf-msft — Mon, 04 Apr 2022 10:09:41 GMT

Hi JaimeHernandezP ,

Please try the following formula:

Measure = VAR count_ = CALCULATE ( DISTINCTCOUNT ( 'Table'[STATUS] ), ALLEXCEPT ( 'Table', 'Table'[DATE] ) ) RETURN IF ( count_ = 1, MAX ( 'Table'[STATUS] ), IF ( MAX ( 'Table'[STATUS] ) = "success", "success" ) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.