# How To Harvest Power BI Slicer Selections To Use Within Other Measures

What I’m looking to achieved here is to showcase how you can create a table on the fly in Power BI, then place a column of that table into a slicer to then extract or ‘harvest’ a value inside a measure. You may watch the full video of this tutorial at the bottom of this blog.

Then be able to insert this into an existing calculation or another measure that you have in your Power BI model.

Previously, the GENERATESERIES/SELECTEDVALUE functions within Power BI did not exist. So this was the old way you had to create simple tables or columns of information that could be used in slicers.

But this is still a great technique to learn and adapt in other situations.

## Harvesting a Value Using a Slicer in Power BI

Let me walk you through the step-by-step process that you can use.

1. Create a table with the total sales measure where total sales is the sum of total revenue.

2. Add a percentage change column in the table.

3. To make it dynamic and to be able to see what the sales will be if it is increased by a certain percentage, create another table. This can be called percent change table.

4. Add the percentage. Let’s say 15%, 20%, 25%, and 30%.

5. Load that in to create the second table and turn it into a slicer.

6. We can use the VALUES function to harvest the values from the Power BI slicer.  Here is the formula:

7. Load the percent selection measure and turn it into a card. The card will show whatever is selected in the slicer.

8. Drag the percent selection into the first table. Whatever is shown in the percent selection card will then be included in the table. In this case, 25% appears in every single row.

Now we have a dynamic calculation. Just select the percent change that you want to utilize.

9. We can then create another measure and call it Total Scenario Sales. And then all we need to do is write a simple logic. This will be Total Sales times by 1 + the percent selection. The formula will look like this.

10. We can now bring out Total Scenario Sales which adds in the percent selection. We can select any of the different selections in the slicer and get the corresponding result.

In the table below, the total scenario sales column shows the total sales increased by 20%.

## Resolving Possible Issues

We encountered a couple of issues while working on the example. In case you experience them too, this is how you can resolve them.

The first issue we encountered was, after creating a card, we get an error when nothing is selected in the slicer. Notice the “X” in the image below.

We need to add a simple logic in the percent selection formula to resolve this. What we can use here is the IF function, with the formula IF(LogicalTest, ResultIfTrue, [ResultIfFalse]).

Using the IF function, we’ve got IF(HASONEVALUE, then we’ll add the same column, (‘Percent Change’ [Percent Change]).

If that is true, then return that result, VALUES(‘Percent Change'[Percent Change]). If not, return zero percent change.

The new formula will be something like this one below.

This will then give you a zero percentage if nothing is selected.

The second issue that we ran into was the table also shows the dates that donâ€™t have any sales.

We want to remove them so that all that will be shown are the dates that have sales. To do this, we need to go back to our percent selection formula and add another logic.

In this case we need to add IF([Total Sales] is equal to blank, then we want it equal to BLANK, and if not, we want it to return the results.

The new formula will then look like this.

We are now able to remove all the blanks and the table shows only the data that we want.

## Conclusion

When using Power BI you sometimes think you are limited to the data sitting within your tables. Well that is certainly not the case. You have tremendous flexibility to build on top of your data by bringing in values from additional tables to flex the numbers or stress your results with scenarios.

There are just so many applications for this in Power BI which is why I call this type of analytical work as building on top of your model.

As you start with the core data that you might extract from your database, bring in all these additional values to adjust results and see what may or may not occur within your results.

These techniques are how you really take your analysis within Power BI to the next level as you have almost unlimited ways to evaluate insights from your initial key metrics.

Check out the video below for more details on this technique (remember similar things can be achieved now with the GENERATESERIES/SELECTEDFUNCTIONS function and with the ‘What If’ parameter feature)

All the best,

Sam

## Power Query: How To Quickly Add Notes

In this post, weâ€™ll learn different ways to add notes in Power Query, which can benefit the people who...

## How To Sort & Filter Chart In Report Builder

In this tutorial, youâ€™ll learn how to sort and filter data in a chart in Report Builder. This tutorial...

## Power BI Filtering Techniques For Tables

In this blog, weâ€™re going to learn about some essential Power BI filtering techniques for tables. We'll...

## Calculating Weekly Sales w/DAX In Power BI

Calculating the difference between weekly sales results in Power BI is unfortunately not that easy. You...

## Three Ways To Use R Script In Power BI

How to Use R In Power BI There are three ways that R can talk to Power BI. This tutorial will teach you...

## Creating Dynamic Ranking Tables Using RANKX In Power BI

In this tutorial, weâ€™ll look at a unique technique inside Power BI that allows us to create dynamic...

## Introduction To Creating An SSRS Bar Chart

Bar charts are a popular way of visualizing data and are often used to compare data across different...

## Find Top Customers Using RANKX In Power BI

Power BI is an amazing analytical engine! In this blog post, I'm going to show you how you can find...

## Power Query Editor: Staging Queries

In this tutorial, we'll learn aboutÂ staging queries using Power Query editor.Â ThisÂ isÂ aÂ new...

## Running Totals In Power BI Using DAX

Sometimes calculating running totals in Power BI is a far better away to analyse trends than just...

## Selenium IDE In Power Automate UI Flows

In this tutorial, we'll discuss the Selenium IDE option in Power Automate UI flows. When creating UI...

## Power BI Slicers Tutorial: Counting Selections Correctly

In today's tutorial, I'm going to work through a subtle issue with Power BI slicers that can trip you...