In a world of AI, Fabric Apps and all fancy things popping up at the moment, I have a need to go a little more back to basics. So in the first part of a bit of a series of what I’m building at the moment, this is how to connect Power BI to Power Automate: Running DAX Queries.
Power Automate gives us simple ways to connect into a semantic model and pull data, but how do you pull the data you actually need?
Let’s start with connecting everything up and getting all the pre-reqs in place!
Power BI Workspace
First of all make sure you have a Power BI report (open still in PBI Desktop), uploaded into a workspace with a connected semantic model. You will also need to ensure the account you are going into Power Automate with has permissions into the workspace.
Then depending on choice the type (I have chosen Scheduled Cloud Flow)
Give your flow a name and if following my Scheduled Cloud Flow add in a Schedule
Select the Plus under Recurrence, which opens the Add an Action tab And search for “Power BI”
Which should give you these options:
The option we need here is:
“Run a query against a dataset.
It will possibly ask you to sign in with an account (make sure to use one that has access to the Power BI workspace from earlier).
This should now give you this!
Next is as simple as choosing the Workspace and Dataset we want to use by using the various dropdowns. The main area we want to focus on is the Query Text.
Back into PowerBI Desktop
Best way now is to jump back into Power BI Desktop (make sure you have the same report open as what is uploaded to the workspace).
Open Optimize and then Performance Analyzer
Start Recording
Refresh Visuals
Select the visual we are wanting to export and you should see a Copy Query button / Run in DAX Query View
Select “Run in DAX Query View” and you will be able to see the raw DAX and the output it produces. For example, in my test data I get the below:
Now remember that Query Text field back in Power Automate? Copy the query from Power BI straight into that box!
And there we go, we now have Power Automate running DAX queries against your model, ready to be used in a number of ways!
A Bit Extra
Let’s just add some extra actions so we can extract that data and test through an email!
Add a “Data Operations” action and then select Dynamic Content to add the data coming from Power BI.
Add a “Send an Email V2” action, and fill in some relevant details such as To, Subject. Select Body and again choose Dynamic Content, but this time the output from the Data Operation.
Great! So let’s now save and test!
Into Outlook, and here it is body of an email!
Looks a little basic, but that’s a problem for another time!
Thanks for reading through! Any questions, feel free to reach out!
But this is going to form the basis for a number of things we can do, whether that’s Power Automate, Copilot Studio, AI actions
I use icons quite regularly as part of conditional formatting whether it’s to show change or highlight certain values. Working with Prism, some of those icons were custom-made. But what I didn’t realise until recently is that they don’t have to be static images.
You can apply the same SVG animation techniques I’ve been experimenting with elsewhere, convert them to base64, and embed them directly into a Power BI theme!
So, keep reading to learn how to add animated icons using Power BI’s conditional formatting.
So where do we start?
I began with a couple of static icons I’d previously built, converted them to SVG, and added a simple transform animation.
Here’s an example SVG: a chevron that switches colours and moves left to right in a loop.
Now let’s embed this into a Power BI theme. If you’re already using a custom theme, you can add to it, or create a new one. Here’s a basic example with several icons:
Did you know that you can update PowerBI parameters with PowerShell across all reports in seconds?
Managing Prism, I rely on parameters for version tracking as well as data sources, making things like infrastructure changes or performing updates much easier. However, managing this at scale meant manual changes was just going to take forever.
Following on from last week’s blog, this post shows how we can automate updating PowerBI parameters with PowerShell.
It’s so much faster!
For example, if you have set a data source via a parameter or another field that may be consistent across your PowerBI solutions. This script will allow you to modify those parameters without having to update each one manually in the PowerBI service.
This script has saved literal hours when deploying updates or infrastructure changes, allowing me to focus on what’s next rather than manually tweaking everything.
Why Automate?
Manually updating parameters across multiple PowerBI workspaces is very time consuming. Automation in this area ensures:
Faster updates when modifying reports and workspaces
Consistency of change across all reports
Less manual effort, freeing up time for the team
How it works?
The script:
Connects to the PowerBI Service
Define the parameter we want to set (change this as required)
Retrieves all PowerBI workspaces or choose to target those with specific IDs
Searches for reports starting with “Aether” (change to what you need)
Reads the parameters attached to a data set
Updates the parameter in the specified position / name (modify this as you need)
Connect-PowerBIServiceAccount
# Updates first parameter - parameter 0
$UpdateParameter0 = "MyNewParameterValue"
$workspaces = Get-PowerBIWorkspace -All
foreach ($workspace in $workspaces) {
# Get reports with names starting with "AETHER" in the current workspace
$Reportlist = Get-PowerBIReport -WorkspaceId $workspace.Id | Where-Object { $_.Name -like 'AETHER*' }
if ($Reportlist) {
Write-Host "Workspace: $($workspace.Name)"
foreach ($Report in $Reportlist) {
Write-Host " Report: $($Report.Name)"
$JsonString = $null
$ParametersJsonString = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($Report.DatasetId)/parameters" -Method Get
$Parameters = (ConvertFrom-Json $ParametersJsonString).value
$UpdateParameterList = @() # Initialize as an empty array
foreach ($Parameter in $Parameters) {
$UpdateParameterList += @{ "name" = $Parameter.name; "newValue" = $Parameter.currentValue }
}
if ($UpdateParameterList.Count -gt 0) { #check if parameters exist.
$currentparam = $UpdateParameterList[0].newValue
Write-Host " Current Parameter 0 Value: $currentparam"
if ($currentparam -ne $UpdateParameter0) {
Write-Host "Parameter 0 value does not match. Updating..."
$UpdateParameterList[0].newValue = $UpdateParameter0
}
else{
Write-Host "Parameter 0 value already matches. Skipping update."
}
$JsonBase = @{ "updateDetails" = $UpdateParameterList }
$JsonString = $JsonBase | ConvertTo-Json
# Update the parameters
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($Report.DatasetId)/Default.UpdateParameters" -Method Post -Body $JsonString
Start-Sleep -Seconds 5
# Refresh the data
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($Report.DatasetId)/refreshes" -Method Post
Write-Host "Refresh started."
}
else{
Write-Host "No parameters found for this dataset."
}
}
} else {
Write-Host "No reports found in workspace: $($workspace.Name)"
}
}
Write-Host "Script completed."
