x amount of failed jobs for yesterday.

[aso1]

I'd like to make a card that checks my servers in "SQL_ID" for failed jobs the last 24 hours and simply reads : "20X FAILED".

Right now I have a report as shown below. But I'd like to make a simple card so I can use it in a dashboard.

as you can see.. accumulated, this would be smarter with a card reading just "20x jobs failed". 

 

in my report right now, i am filtering it for a relative day -1.. to see the failed jobs for last day only. 

here is my data (note the date_in.. that the time for when I get the data)

thanks all!

[mwegener]

Hi @aso1 ,

was your problem solved?

[v-yiruan-msft]

Hi @aso1 ,

You can create a measure as below:

Measure =
VAR _countofFail =
    CALCULATE (
        COUNT ( 'Server run'[server] ),
        FILTER (
            ALL ( 'Server run' ),
            'Server run'[status] = "Fail"
                && DATEDIFF ( 'Server run'[date_in], TODAY (), DAY ) = 1
        )
    )
RETURN
    CONCATENATE ( _countofFail, " jobs failed." )

Best Regards

Rena

[aso1]

Hi Rena, 

Thank you for this suggestion. 

I made the measure and added the measure to a card. 

but the card says 'Ok'... which it is not. You can see in 'Status' that we had some of our databases with status being "CheckDB should be performed".    

Therefore, the card should write "Error". 

The card should only write "Ok" if 100% of all elemenst in 'status', within 1 day = "Ok".        If not, then write Error.

[v-yiruan-msft]

Hi @aso1 ,

What's your expected result? The status for per database in a day or other? Please check whether the below measure is what you want:

LastStatus =
VAR _tcount =
    CALCULATE (
        COUNT ( 'Integrity'[date_in] ),
        FILTER (
            ALL ( 'Integrity' ),
            'Integrity'[Database] = MAX ( 'Integrity'[Database] )
                && FORMAT ( 'Integrity'[date_in], "mm-dd-yyyy" )
                    = FORMAT ( SELECTEDVALUE ( 'Date'[Date] ), "mm-dd-yyyy" )
        )
    )
VAR _okcount =
    CALCULATE (
        COUNT ( 'Integrity'[date_in] ),
        FILTER (
            ALL ( 'Integrity' ),
            'Integrity'[Database] = MAX ( 'Integrity'[Database] )
                && FORMAT ( 'Integrity'[date_in], "mm-dd-yyyy" )
                    = FORMAT ( SELECTEDVALUE ( 'Date'[Date] ), "mm-dd-yyyy" )
                && 'Integrity'[Status] = "Ok"
        )
    )
RETURN
    IF ( _okcount = _tcount, "Ok", "Fail" )

Best Regards

Rena

[Fowmy]

@aso1 

This solution can be modified to suit your need: https://community.powerbi.com/t5/Desktop/Last-24-Hours-Meter-Outages-Bar-Chart/td-p/586236

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[amitchandak]

@aso1 , these are various way you can get last day data. Example

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf])  ),Table[Date]) ,Table[Date],Day)

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))
Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

Refer: https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.