Power BI is an extraordinary tool for helping business users interact with their data and potentially set goals for how they want to proceed. However, when the goal setting process is completed in Power BI – there is currently no in-program way to “capture” each user’s input. Let’s change that while we consider this example:

A business user reviews his or her data in Power BI and is asked to set 3 goals for categories “Black”, “Green”, and “Red”.

These goals are set by changing each of 3 disconnected slicers in the Power BI report -which then cause changes in the visible chart. It’s important to recognize that these goals are ONLY visible in the user’s Power BI report session as the direct result of manipulating the slicers.

But what happens if someone besides the user also wants to be aware of these goals. Currently – only the user can see these values (which are unique to their user account) and not saved to the underlying dataset. If the user happens to hit this reset button the values would be lost.

Let’s explore a method for taking these (or any) selected values in Power BI and sending them to an external source – where they can be archived and accessed for future reference.

This idea is an extension of work done previously by Ashley’s BI Blog and Parker Stevens. In short – it’s the realization that we can add things as query parameters to a URL string (including our selected values) – and retrieve them outside of Power BI.

Here’s how it works:

First we need to capture the values that we want to send outside of Power BI. We can accomplish that using a SELECTEDVALUE() measure. 

Black =
SELECTEDVALUE (
    'Black Goal'[Goal],
    0
)

It’s important that we specify an alternative value for this formula (in this example, it’s 0) so that it doesn’t return a blank – which could break the URL string.

Next we need to create a destination for our Data. In this example – I’m using an Excel File saved in Onedrive for Business – but this could really be just about anything (Sharepoint list… etc).

To that Excel file, we need to create a table with columns for the specific data we want to collect. In this example we are going to collect the Time/Date, the email of the user submitting the data, and the inputs of 3 slicer values (Black, Green, Red).

Next we are going to create this 2 step flow in Power Automate

For the first step: we need to change the METHOD to “GET”

After that we’ll add a step to Add a Row to an Excel-based Table. When we connect to our previously created table – we’ll be able to see each of the specified columns:

Now we’ll save the flow – which will generate a URL in our first step. We need to copy that URL.

Back in Power BI – here is where the magic comes in. We can add our values to this URL by giving each a unique name and appending them to the URL using the following format:

In short – we can put ANY VALUES here – so long as they do not have spaces or special characters that might ‘break’ the URL from working correctly.

Let’s write a measure using the base URL we copied from Power Automate and then add our measures which grab the values from each of our 3 slicers (Black, Green, Red). Additionally – we will add a fourth value to show the identity of the user submitting the data. We’ll use USERPRINCIPALNAME() to generate that.

Here’s what that would look like.

Using our example above – the additions after our BASE URL would look like this: …&Black=12&Green=3&Red=8&user=swise@3cloudsolutions.com

Now – let’s head back to Power Automate to set up how to extract these values out:

In all cases – we will need to specify dynamic content expressions. For DateTime – we’ll use utcNow()

The remaining columns all follow the same pattern.

TriggerOutputs()[‘Queries’].uniquename

The UNIQUENAME is the name we assigned to that value in the URL. If we wanted to retrieve the “USER” value from our query – we would enter:

Likewise to retrieve our 3 slicer values – it would be: 

triggerOutputs()['Queries'].Black
triggerOutputs()['Queries'].Green
triggerOutputs()['Queries'].Red

That’s it! Now save the Flow.

Let’s test this by adding a button to our Power BI report – that points to our URL formula:

That’s it! Now publish the Power BI report to the service – and let’s give it a try:

When we push the button – it kicks off the URL…

And adds the slicer values to our Excel online sheet!

It works! Very cool!

Now suppose we want to capture the ACTUAL amended chart value – and not the slicer value.

Remember this chart value is calculated on the report screen as the addition of the user’s slicer selection to the original chart value.

To retrieve these values – we simply need to amend the “VALUE” component of our URL formula to specify these chart values.

Here I’ve amended the URL formula to show the amended “goal” chart value (which is the slicer value added to the original chart value)

And after publishing the changes to the service – the new numbers are being conveyed correctly!

This trick will undoubtedly open up a ton of new opportunities for report creators to ‘capture’ information about what users are doing in a specific report session. I’m sure I’ll be digging into this concept some more as I try to explore the full range of how it can be used.

Cheers!