In my last two posts (see here and here) I showed you how you can call the new endpoints in the Power BI REST API for exporting a report. There are some obvious, extremely useful applications for this such as emailing PDF exports of a report out to large groups of users. In this post, however, I’ll show you how you can use this for something different: creating a Power BI alerting solution.
Now I know what you’re thinking: we already have alerts in Power BI and we can also trigger a Power Automate flow when an alert fires. This functionality is a bit limited though: you can only create an alert on a dashboard, not a report, and then only on some visuals; what’s more you can only use fairly simple rules to trigger an alert. You may need something more sophisticated, but while Power Automate would seem to be the perfect tool for building an alerting solution there’s another problem: it isn’t possible to query a Power BI dataset from Power Automate… until now.
How? The new export endpoints in the Power BI REST API allow you to export a paginated report to a number of different formats, one of which is XML. This means you can write any DAX query you want, use it in a table in a paginated report, export the paginated report to XML in Power Automate and bingo – you have the output of the query in a format that Power Automate can read and do something useful with.
Let’s see an example. Take the following table of data in a Power BI dataset:
It shows sales for different countries, and let’s say that if the sales value exceeds the threshold given threshold then you want to send an email to the address given in the last column.
The following DAX query filters this table to return all the rows where sales is greater than the threshold:
EVALUATE FILTER( 'Sales', 'Sales'[Sales]>'Sales'[Threshold] )
It’s quite easy to create a basic paginated report in Power BI Report Builder with just a single tablix to display the output of this query:
Now, let’s take the Power Automate flow that I described in my last post and alter it slightly.
First of all, instead of exporting to CSV as I did last time, you need to change the action that calls the Export To File endpoint to export the report to XML:
More substantial changes are needed at the end of the flow, where the exported report is returned. Here’s what this part of the flow looks like at a high level after the changes:
The first action shown here, Download the exported report file, gets the XML returned from Power BI. The SSRS documentation has a lot of detail about how a report gets rendered to XML here, but by keeping the report very basic it’s easy to understand the format of the XML. Here’s what gets returned in this case:
<?xml version="1.0" encoding="UTF-8"?>
<Report
xmlns="AlertsPaginatedReport"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="AlertsPaginatedReport
https://redacted"
Name="AlertsPaginatedReport">
<Tablix1>
<Details_Collection>
<Details Country="UK" Sales="5"
Threshold="2" Email="chris@mycompany.com" />
<Details Country="New Zealand" Sales="6"
Threshold="1" Email="phil@mycompany.com" />
<Details Country="Netherlands" Sales="5"
Threshold="2" Email="kasper@mycompany.com" />
</Details_Collection>
</Tablix1>
</Report>
The approach I’ve used to consume this XML in Power Automate is basically the one described in this post I found. The Compose action uses an XPath query to return an array containing one item for each row in the query results; writing the XPath query was quite painful but I finally got it working. Here’s the expression from the action:
xpath(
xml(body('Download_the_exported_report_file')),
'/*[local-name() = ''Report'']/*[local-name() = ''Tablix1'']/*[local-name() = ''Details_Collection'']/*[local-name() = ''Details'']')
Finally, inside this loop, another Compose action retrieves the email address from the current iteration and this is then used to send an email:
Here’s the expression used in the Get Email Address action:
xpath( xml(item()), 'string(/*[local-name() = ''Details'']/@Email)')
