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
Creating Animated Line Charts in Power BI can be done in several ways. The most common approaches are using SVGs with measures and cards, or leveraging 3rd-party custom visuals that render HTML. While SVGs combined with a card look visually impressive, they often lose some of the interactivity we expect from native visuals such as tooltips.
To solve this, we can combine native visuals with SVGs, layering them together to create animated line charts that retain interactivity.
For this method, we’ll use:
A line chart supported by measures that provide the line itself plus space above and below it.
An animated SVG background placed behind the chart.
Step 1: Create the measures
For this example, we’ll use a dataset that tracks costs over time. We need three measures to build our animated line chart
Measure 1 (The actual cost)
This initial measure will simply be the actual value, so the cost column. In this instance I have just loaded into a measure called [COST]
COST = SUM(ProductSales[Cost])
Measure 2 (The Line)
To create the line we want to highlight, we need it to sit just above the actual cost. We do this by creating a new measure that takes the cost for each date and returns 4% of it, making a thin but visible line on the chart.
The final measure fills the remaining space on the Y axis. This makes the chart look full while leaving about 30% of space above the line. We implement this measure as follows
With the measures in place, we can start building the visual. The steps below show how to use the measures to create the chart.
Add a Stacked Area Chart
On the Y axis, add the three measures in this order: COST, COST_LINE, COST_Y_MAX
On the X axis add a Date column from your dataset
Once added, your chart should look something like the example below.
Next, we need the COST_Y_MAX measure to fill the top part of the chart. To do this:
Select the visual and go to Y Axis, Range.
Set the maximum value to COST_Y_MAX.
This ensures the chart fills the Y axis correctly and leaves space above the line.
Step 3: Format the chart layers
Now that we have the basic chart, it’s time to set up the visual elements that will support the layers behind it.
Select the visual and disable the legend.
Under X & Y Axis, disable the titles (optional if you don’t need them).
Under General, Background, choose a background color. In this example, I’ve used #292929. Make a note of this, as we’ll be turning the background off later.
After these changes, your visual should look something like this:
Next we need to use that same background for our shade areas and lines
Under Lines
Select the COST_Y_MAX Series and untick show for this series
Select the COST Series and untick show for this series as well
Under Shade Area
Select the COST_Y_MAX Series, untick match line color
Set the Color to the same as your background color (e.g. #292929) and set transparency to 0%
Select the COST Series and again untick match line color
Set the Color to the same as your background color (e.g. #292929) but this time set transparency to 5%
Select the COST_LINE Series and uncheck Show for this Series
That should now give us the following
Since we used the other measures to hide parts of the chart, the next step is to fully configure the line area we want to display.
Under Line, with Series All selected, set the Interpolation Type to Smooth and the Smooth type to Monotone.
Under Line, select COST_LINE and uncheck Show for this series.
Finally, go to General, Effects, Background and turn the background off.
This prepares the chart so the animated SVG we’ll add behind it will show correctly.
At this point, our visual is ready for the SVG animation to be added behind it.
For the background, I’ve used the same dynamic SVG setup from a previous post, but applied a different animation in the measure. The steps below show the updated animation code.
Next, we create a measure to generate the animated SVG background. This measure lets us pick different colors or gradients dynamically and animates a rectangle across the visual.
In this step we need to setup the card to be able to apply the measure. As with my other examples I have been using the new card visual.
Firstly, on the card, apply the measure under the image section.
Select Image URL
Select the measure
Add a column / value to the card to the Data parameter (any is fine we are going to remove this from view)
Next we need to resize the card to the full size of the window (or at least the area wanting to use) and then disable the values / labels under the Callout values section:
Disable Values
Disable Label
Now all padding needs to be removed from the card as well as removing all existing backgrounds / style effects
Under Visual / Cards:
Disable Background
Disable Border
Change Padding to custom and set all to 0px
Under Visual / Images
Change Space between Image and callout to 0px
Under General / Effects
Disable Background
Under General / Properties
Set all Padding to 0px
Once added we should now have the two components, the visual we built and the card with the animated background
Step 6: Layer the visuals together
Now for the fun part. We need to place the animated card behind the visual and resize it so it matches the border of the background color we applied to the top and bottom of the chart.
Once positioned correctly, the animation will show through, and you may notice it animate a few times as you adjust the sizing.
Final polish
And that is it. Everything is in place. The visual, the animated background, and the measure controlling it. You can have the animation show a single color, a gradient, or any variation you like. I have tried a few different options myself as you might have seen on my LinkedIn.
Once it was set up, I tidied the visual by adjusting the title and axes, turning off the gridlines, and adding a custom tooltip. Using the built-in tooltips would have shown all the extra measures we added, so the custom tooltip keeps it clean and focused.
One of the things I hear the most when running demos or working with customers.
“If I click on that KPI will that then filter the report”
Sadly, no. Or well not by default. Kick in Product Manager brain, make it work!
So in this blog post I will be running through my implementation of creating cards that can do exactly that, be clicked on, filter the rest of the report and fit nicely with the overall aesthetic I’ve built with Prism. We can also throw in some SVG animation to enhance everything visually and give it that extra level of polish.
So, how do we make KPI cards clickable?
Power BI doesn’t natively allow KPIs to act as slicers, so we need a workaround.
To be able to click and filter in PowerBI we have a few slicer options, but to make it look like a KPI card that’s when we need to consider some options:
We could:
Layer it (create a card and then add a dummy button slicer on top)
Use a Button Slicer
In this example, I’ve gone with the button slicer approach. It integrates cleanly into the overall product UI and is easier to manage as I evolve the design further.
The trick: SVG + Measures = Interactive KPI
In some previous posts, Ive used HTML / SVG with measures that we can then add to the image url field. Ive used the same logic here but then also added a state for Static, Hover and Selected. As we also need to think about making it obvious for end users to see we’ve added a filter.
Some subtle icons and animation can be really effective here
Tutorial
To start off:
Create a button slicer
Add the 3 different measures below
Below are my SVG measures. Each one displays a simple KPI-style card with a filter icon and adjusts styling such as colour and animation based on state (Static, Hover, Selected). The end result looks something like this:
Once you have added your button slicer and added the above measures, we need to apply them to the different states and ensure all looks and works as required.
With the button slicer selected, first we need to add a field. This field needs to be what you are looking to filter on.
In my example, I used a calculated column linked to my table that was simply filtered on a 1 or 0 depending on the conditions – then linked to the wider data. (The data I used was looking at allocation rates, over 50% was a 1 everything else a 0)
Next:
On the filters if required set the filter on that visual to show what you are looking to filter on (i.e “1”)
Under layout, we need to show only the 1 row and column and I generally set the space between buttons as 0
Now to prepare for using the image measures we need to hide everything else
Under General
Disable Title
Disable Header Icons
Disable Effects / Background
Under Visual / Buttons
Disable Border
Disable Full
Padding – Set to custom and set all to 0 px
Under Callout Values
Disable Values
You should now essentially have a box that shows nothing!
Now back under visual / Image we need to apply the 3 measures – apply the following based on the state
All
Hover
Selected
HTML_Card_Filter
HTML_Card_Hover
HTML_Card_Selected
With all those added you should now have you Card Slicer!
Now you will need to use fields and titles that make sense to your data, those can all be changed within the measures themselves, just dont forget to change in all 3!
Once complete you should end up with something like the following:
Why This Matters
Beyond just looking nice, this pattern supports:
Design flexibility: Themed, responsive KPIs tailored to your report’s look and feel
Better UX: Users get clear, visual feedback
Faster interactions: No more guessing what’s clickable
Thanks for reading! As always I would love to hear from you, as well as any ideas you have!
