If you use Power BI, you're probably familiar with the data driven alert functionality.
With data driven alerts, you can set alarms on tiles on your dashboards, so that you get notified when data changes beyond the limits you set.
I think you all know already that I love doing integrations between Power Platform tools, and my all time favourite is probably Power BI+Power Automate.
In this post, I will walk you on how you can set up a data driven alert, and then customise it to do escalations using Outlook and Teams.
How are we going to do this?
First, we will create a data driven alert in Power BI Service. This alert will be triggered every time it reaches a defined threshold.
After that, we will create a flow in Power Automate. This flow will also be triggered alongside the alert in Power BI.
Once triggered, the flow will check the value associated with that alert, and then take different actions based on the different rules I defined.
In my example I used a sales data sample. I've set up an alert in Power BI Service that will be triggered when the sales value is lower than 110M, this is the threshold that I defined. Power Automate will then also be triggered, taking different actions depending on the sales value:
> If the sales value is higher than 100M, then it will post a warning message on the Sales Teams Channel
> If the sales value higher than 80M and lower than 100M, then it will post a warning message on a Sales Teams Channel and send an email to the Sales Supervisor
> If the value is lower than 80M, it will post a warning message on the Sales Teams Channel and escalate the issue sending an email to both the Sales Supervisor and the Sales Manager
The workflow will look like this:
1) Set up a data driven alert in Power BI Service.
As mentioned in the notes (please check in the end of this blog post), to set up a data driven alert, you need a visual of the type gauge, KPI or card. Please pin one of these to your dashboard so that you can use it for the data driven alert.
Follow the steps in the video below to set up your alert:
> On your dashboard tile, click on the three dots on the right upper corner of the tile
> Click on Manage Alerts
> Click on Add Alert Rule
> Give a name to your alert and setup the condition and the threshold
> Select the maximum notification frequency (e.g. if you choose "At most every 24 hours" you will receive no more than one alert notification per day)
> Deactivate the "Send email too" option (we will be doing this using Power Automate)
> Save and Close
2) Create an Automated Cloud Flow.
In this step, we will be creating an automated cloud flow.
This flow allows you to create an automation that is triggered by an even, in our case it will be triggered by our previously setup Power BI data driven alert. To enable this, we will be using a connector to Power BI Service, that will enable both tools to "talk" to each other.
To create our Flow, we will start going to Microsoft Power Automate | Microsoft Power Platform.
Next, we will follow the steps below:
> On Power Automate, click on Create
> Choose Automated Cloud Flow
> Give your Flow a name and under the Flow's trigger search for "Power BI" and choose "When a data driven alert is triggered"
> Click on Create
Lastly, on the "When a data driven alert is triggered" step, select the Alert ID you previously created on Power BI Service. In my case I have created an alert named "Alert for sales", so this will be the one I'm using:
3) Configure the first condition of your Flow to post a message to a Teams channel
Now that we have our trigger, it's time to start building up our Flow.
We want the alerts to be escalated based on the current value of the tile. The good thing is, there are a few parameters that can be passed to the next steps based on our previous step "When a data driven alert is triggered". This usually happens whenever you add a new step to your flow, meaning that you can usually "reuse" values from previous steps! This makes life so much easier!
> Click on New Step
> Select Condition (if it doesn't show up right away, try search for it)
> On the first Value for the condition choose Tile Value
> On the Condition choose "is greater then or equal to"
> Write the second Value of your condition
> On the If Yes branch, search for Teams and select "Post a message as the Flow bot to a channel"
> Select the Team, the Channel, the message and the headline you will want on your bot card. Remember, you can choose values from the "When a data driven alert is triggered" step like the Tile Value, The threshold you defined for the alert, the alert tittle, the alert URL (link to the dashboard)
> Leave the If No branch empty. If the condition defined is not met, then we don't want our flow to take any further action
4) Configure the second condition of your Flow to post a message to a Teams channel and to send an email in Outlook
We will now be building our second condition and setting up some more actions on our flow.
> Click on + sign above the Condition step
> Select Add a parallel branch
> Select Condition (if it doesn't show up right away, try search for it)
> On the first Value for the condition choose Tile Value
> On the Condition choose "is greater then or equal to"
> Write the second Value of your condition
> Click on Add and next on Add row
> As with the first row, on the first Value for the condition choose Tile Value
> On the Condition choose "is less than" and write the second value of this condition row
> Under the If Yes branch, search for Teams and select "Post a message as the Flow bot to a channel"
> Select the Team, the Channel, the message and the headline you will want on your bot card
> Click on Add an action and search for Outlook
> Select Send an email (V2) and fill in the To, Subject, and Body fields. Remember, you can choose values from the "When a data driven alert is triggered" step like the Tile Value, The threshold you defined for the alert, the alert tittle, the alert URL (link to the dashboard). If you click on Advanced Options it will show you extra fields like CC, Importance, attachments...
> Leave the If No branch empty. If the condition defined is not met, then we don't want our flow to take any further action
To build the third escalation point of this flow (post a message on the Sales Teams Site and send an email to both the Sales Supervisor and the Sales Manager, you just need to follow the same steps was we did previously in 4).
And yes, you can send emails using the outlook connector to multiple people! just write the names down and Active Directory will do the rest for you :)
+ Go the extra mile +
You can add so many different actions to your flows that if I could talk about all of them I would be here all day! But one that I usually get asked a lot is sending text messages to phones, try the Twilio or Click Send actions to automatically send text messages with your Power BI data driven alerts (this might have additional costs).
About licensing:
If you want to know more about the licensing requirements to use Power Automate, check the link below:
Licensing guide:
https://go.microsoft.com/fwlink/?linkid=2085130
Notes:
> You can set alerts if someone shares a dashboard that's in a Premium capacity
> You can set alerts on tiles in any workspace if you have a Power BI Pro license
> Alerts can only be set on tiles pinned from report visuals, and only on gauges, KPIs, and cards
> Alerts can be set on visuals created from streaming datasets that you pin from a report to a dashboard however, alerts can't be set on streaming tiles created directly on the dashboard using Add tile > Custom streaming data
> Alerts aren't supported for card tiles with date/time measures
> Alerts only work with numeric data types
> Alerts only work on refreshed data. They don't work on static data