To query a Power BI Service semantic model, you need access to the dataset. There are several ways to do this, since it ultimately involves sending a request through the Power BI REST API.
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group
Although we'll use Power Automate in this example, you could just as easily use an Azure Function or any other service capable of making API requests.
The first step is to verify that your DAX query returns the expected results. A convenient way to do this is with DAX Studio, which allows you to execute queries against tabular semantic models. If your dataset is hosted on a dedicated capacity, you can connect to it directly. If you're using Power BI Pro, you can open the original Power BI Desktop (.pbix) file and connect DAX Studio to it instead.
In my case, I want to receive a daily update every morning showing how this year's sales are performing. To do that, I'll execute a measure that returns a single row and a single column, filtered and formatted as needed. The DAX query looks something like this:
EVALUATE
SUMMARIZE(
FILTER('Orders',
RELATED('Tablecalendar'[Year])= YEAR(NOW()))
, "Venta", FORMAT( SUM(Orders[Sales]), "#,0.00")
)I'll sum the sales from my fact table and filter them by the year of the current date using the related Calendar table. I'll also format the result with thousands separators and two decimal places.
Once I've confirmed the expected value, I can open Power Automate and create a scheduled (recurring) flow.
Next, we'll add the Run a query against a dataset action. This performs the same function as the Power BI REST API endpoint mentioned earlier. To process the tabular results, we'll add a Create CSV table action or Create HTML table, which converts the query output into a table. This gives us a structured output that we can use when composing the notification.
The Run a query against a dataset action lets you select the workspace, the semantic model (dataset), and provides a text box where you can paste the DAX query. For the Create CSV table action, we'll use only the first row, since the query is expected to return a single value.
Note: If you want to build a more complex table, you can refer to the Power Automate documentation or community forums for additional guidance.
Finally, we can send the results by email or as a Microsoft Teams message to a group or channel, ensuring the relevant stakeholders are kept informed about sales performance. Simply add the Output dynamic content from the Create CSV table action to the body of the email or Teams message.
Using this approach, you can create alerts or notifications for any result returned by a DAX query against a Power BI semantic model.
Note: None of the Power Automate actions used in this solution require a Premium license. You can build the entire flow using the standard Power Automate features included with Microsoft 365.
I hope you find this useful for staying informed about the metrics that matter most through the communication channel of your choice, without relying solely on Power BI email subscriptions or mobile app notifications. I'm currently using this solution as main alert solution from KPIs.
Original post in spanish at LaDataWeb
Super User
