Set up alerts on transaction per hour

[Anonymous]

I have a table that logs transaction with a time/date stamp, (approx 300 per hour)I would like to build a report/kpi that would send alerts if the average transactions per hour drop below a certain threshold. 

The transaction table also has a status column (Okay, Failed, Timeout) and would like to have a similar kpi alert if failed or time outs reach a certain rate. 

Any help would be appreciated. 

Ray 

[v-sihou-msft · Accepted solution]

@Anonymous

For your requirement, you need to add a "Date-Hour" column in your dataset. In Query Editor, you can calculate "Start of Hour" then concatenate with Date part into a datetime.

Then you just need to count all transactions divide by distinct count of Date-Hours to get average transactions per hour.

average transactions per hour =
CALCULATE ( COUNTA ( Table[transaction] ) )
    / CALCULATE ( DISTINCTCOUNT ( Table[Date-Hour] ) )

Then you create a Card visual with above measure and configure Data Alert on Power BI Service.

Regards,

[Anonymous]

Thanks, that works perfectly.