I have a very simple problem, with a slightly complicated solution, and an inconsistent function 🙂
Simple Problem: We have locations across the US. If there is a safety issue at one of these locations like someone slips, a report is made through a Microsoft Form to report it. If the safety issue is severe enough (someone slipped into a vat of acid) that report is categorized as a Major Safety event. When this happens, I want PBI to send a data alert email to the managers of that location, regional managers overseeing that region and national leaders.
Slightly Complicated Solution: Since these events can need quick action, I have indicators set to capture these major safety events. I also have the data set to refresh hourly. The model looks at the time of the event and the time of the refresh and compares the two. If the event submission is within the previous hour, the event should trigger the data alert. There initially were issues with timezones (UTC service vs EST desktop, etc) but I believe those are fixed. The model uses RLS to ensure that the alerts are sent to the people that oversee the location of the report.
Inconsistent Function: When it works, it works great, but I've noticed some odd extended pauses in the data alerts. The safety reports are a many-a-day frequency but thankfully most of them are mundane. However, even though I haven't changed anything on the dashboard and I don't believe anyone else has, and the database refreshes seem to happen hourly without issue, there have been weeks where the alerting system just doesn't activate. Emails aren't sent, notifications don't ping.
Request: Are there any ideas on further investigation that I can do? Is there a limit on using the alert function? I'm doing hourly updates and the RLS has to check about 200 users against any data alerts that come up. Are there logs I could look into? Are there any known issues with the Alert function? Any guidance is appreciated! 🙂
--edit: Adding more descriptive step by step of process --
Sure - the process is:
Event Submitted to MS Forms (12:45pm)
Dataset Refresh (1:30pm)
MS Forms table refreshed in Dataset (1:30pm)
Timestamp of refresh converted to number: 3/31/2023 1:30pm to 2023033113) (round to hour)
Timestamp of submission same 3/31/2023 12:45 to 2023033112) (round to hour)
Pseudocode: IF (RefreshTimestamp -1 = SubmissionTimestamp, "New","")
Pseudocode: NewCountMeasure = COUNT ([New])
Report Indicator: NewCountMeasure (usually 0 until a new submission)
Pin report indicator to dashboard
Set dashboard alert to IF (NewCountMeasure > 0) then Alert
[x] Send Email when alert
Everything is done though dashboard alerts, not using Powerapps.
This is interesting as I was not aware you could use RLS to control who got the notifications/emails for dashboard alerts. I thought only the person that setup the alert, got the alert. I would be interested in how you get this resolved.
The RLS controls the data which flows into the indicator which is then pinned to the dashboard. So if the event is in New York City, only the NYC folks, Northeast regional team and national team can see the data. Users still have to create the alert themselves, but the RLS works.