Harnessing the Power of the “Run a Query Against a Dataset” Trigger in Power Automate
- Admin
- 1 day ago
- 5 min read
If you’re already familiar with Power BI and Power Automate, you know how powerful it is when these two tools work together. But one of the most game-changing triggers in Power Automate is:
👉 “Run a query against a dataset”
This trigger lets you execute DAX queries (Data Analysis Expressions) directly against your Power BI dataset from a flow. That means you can extract aggregated results, row-level values, or KPIs in real time — and then use that information to trigger actions, automate workflows, or notify stakeholders instantly.
In this guide, we’ll explore:
What this trigger does and why it’s so powerful.
A step-by-step example: automatically sending an alert when sales drop below a target.
A walkthrough of all the actions and connectors involved.
Additional ideas on how you can use this approach in your own organization.
Why This Trigger Matters
Most Power Automate/Power BI integrations rely on refresh triggers (like “When a dataset refreshes”) or manual button flows. But those are limited: they don’t give you data-driven conditions.
With “Run a query against a dataset”, you can:
Query exact KPIs or metrics without exporting entire tables.
Dynamically filter data using DAX expressions.
Chain automation: “If sales < 100k this week → notify sales manager via Teams and create a task in Planner.”
Avoid manual checks — your flows become self-aware of the data inside Power BI.
💡 Pro Tip: You don’t need to query the entire dataset — keep queries simple and optimized. For example, use SUMMARIZECOLUMNS or single measure evaluations for faster results.
The Example Scenario
Let’s walk through a practical use case.
Business Goal:Your sales team wants an automatic email alert if total sales for the current month fall below €100,000.
Instead of manually checking the dashboard, you’ll use Power Automate to:
Run a DAX query against your Power BI dataset.
Retrieve the monthly sales figure.
Check if it’s below the threshold.
Send an email and Teams notification if true.
Step-by-Step: Building the Flow
Here’s how to build this flow from scratch:
Step 1 – Create a New Flow
Go to Power Automate.
Choose “Scheduled cloud flow” → run it daily at 8 AM (you can adjust frequency).
This ensures the dataset is checked regularly without manual effort.
Step 2 – Add the Power BI Action: Run a Query Against a Dataset
Click + New Step → Search for Power BI.
Select: Run a query against a dataset.
You’ll be prompted for:
Workspace → choose where your dataset lives.
Dataset → pick the correct model (e.g., Sales_Model).
DAX Query → enter the DAX expression to return the result you need.
Here’s a simple example DAX query:
EVALUATE
ROW("MonthlySales",
CALCULATE(
SUM(Sales[Amount]),
DATESMTD('Date'[Date])
)
)
This query creates a one-row table with a single column MonthlySales showing sales for the current month.
Step 3 – Parse the Query Result
The query returns JSON, so you’ll need to extract the value.
Add the “Parse JSON” action.
From the dynamic content, select the query output.
Use the following schema (adjust column name if different):
{
"type": "object",
"properties": {
"tables": {
"type": "array",
"items": {
"type": "object",
"properties": {
"rows": {
"type": "array",
"items": {
"type": "object",
"properties": {
"MonthlySales": { "type": "number" }
}
}
}
}
}
}
}
}
Step 4 – Add a Condition
Now that we have MonthlySales, let’s check the threshold.
Add a Condition action.
Expression:
body('Parse_JSON')?['tables'][0]['rows'][0]['MonthlySales'] < 100000
If Yes → send alert.
If No → do nothing (or log status).
Step 5 – Send the Alert
For the Yes branch, add actions like:
Send an email (Outlook connector) → Notify finance & sales managers.
Post a message in Teams → Alert the sales channel with the figure.
Create a Planner task → Assign follow-up actions automatically.
💡 Pro Tip: Format your message to include the sales value dynamically. Example:“⚠️ Monthly sales are at €85,400 — below the target of €100,000. Please review the pipeline immediately.”
How It Works Behind the Scenes
DAX Query Execution → Power BI executes the query against the in-service dataset.
Data Serialization → The result is returned in JSON format.
Logic Evaluation → Power Automate interprets the result with conditions and branching.
Action Chain → Depending on logic, downstream actions (emails, Teams, Planner) are triggered.
In essence, you’re turning Power BI data into a flow trigger without needing to export data or manually refresh reports.
Other Possibilities
This example uses a sales threshold, but you can apply this to many scenarios:
Data Quality Checks
Query if there are any blank customer IDs.
If count > 0 → log issue in SharePoint list or send alert.
Performance Monitoring
Query if average response time > SLA limit.
Send alert or escalate.
Financial KPIs
Track margin %, EBITDA, or cash flow.
Alert CFO if a KPI drops below tolerance.
Compliance & Audits
Check if number of overdue compliance trainings > 5.
Automatically assign reminders in Teams.
Automated Reporting
Extract KPI values daily.
Write them into an Excel file or SharePoint list for archive.
💡 Pro Tip: You can combine multiple DAX queries in one flow to check several KPIs at once.
Best Practices for Using This Trigger
Keep Queries Simple – Avoid heavy queries that stress the dataset. Stick to summarized results.
Use Measures – If KPIs already exist in Power BI, reference them directly in your query.
Schedule Wisely – Don’t hammer the dataset with overly frequent queries (e.g., every 1 minute).
Error Handling – Add a “Configure Run After” step for failed queries so the flow logs errors.
Document Your Queries – Store DAX snippets in a shared OneNote/SharePoint for reuse.
Wrapping Up
The “Run a query against a dataset” trigger in Power Automate is one of the most powerful ways to make your reports actionable. Instead of just viewing KPIs, you can now act on them automatically.
In our example, we built a flow that:
Checked monthly sales via DAX.
Evaluated against a threshold.
Triggered alerts and tasks automatically.
This approach can be applied to finance, sales, operations, or compliance scenarios. It transforms Power BI from a reporting tool into a real-time decision engine.
So, next time you want to go beyond dashboards, remember:👉 Don’t just measure your KPIs — make them act for you.
Do you need a starter Power Automate package with some how-to guide, best functions and how to appy them in Power Automate? Just with a slight adjustment to the quick and efficient flows. Check it out.
Comments