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.

Total Sales Measure

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.

Sample slicer in Power BI

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

Using values function to harvest values from a slicer in Power BI

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.

twenty five percent

 

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.

Screen Shot 2019-08-14 at 9.45.27 AM

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%.

total scenario sales increased by 20 percent2

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.

Card shows error when nothing is selected in the Power BI slicer

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.

Percent Selection formula with If function logic

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

Card error resolved after adding a logic

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

 
table without 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.

Screen Shot 2019-07-31 at 4.19.19 PM

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

Screen Shot 2019-08-14 at 9.33.06 AM

***** Related Links *****
Quick Measures Deep Dive – How To Use Them in Power BI Models
Implementing DAX Measure Groups Into Your Power BI Reports – Modeling Review
Calculating Percent Profit Margins Using DAX in Power BI

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

 

Enterprise DNA Power BI On-Demand

The Latest

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and…

Python Inline If: Simplify Your Conditional Expressions

You’ve been cruising through your Python journey, slicing through lists, taming those wild tuples, and maybe even wrestling with a…

Python Empty String: Understanding and Handling It Effectively

Power BI financial dashboards provide a quick and easy way to monitor an organization’s financial performance in real-time. By consolidating…

Power BI Financial Dashboard Examples: Key Insights for Businesses

When working with integers in Python, you should know the maximum value your code can handle. This will depend on…

Python Max Int: Understanding Maximum Integer Limits

Pi is a fundamental mathematical constant that represents the ratio of a circle’s circumference to its diameter. Leveraging Pi in…

4 Ways to Use Pi in Python With Examples

ChatGPT is an advanced AI-powered tool that can transform the way you write code. Developed by OpenAI, ChatGPT accelerates your…

ChatGPT for Coding: A Guide With Practical Examples

When working with data projects in Python, you are most likely to use datetime functions. Almost every dataset that you’ll…

Python datetime, a comprehensive guide with examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various sources. One of…

Power BI Themes: How to Customize Your Reports with Ease

With the advent of ChatGPT, individuals and businesses worldwide have been using it to simplify their daily tasks and boost…

ChatGPT for Data Scientists: Unleashing AI-driven Insights

Staying ahead of the curve in data analysis is essential to your success in business. One of the most innovative…

ChatGPT for Data Analysts: Revolutionizing Insights and Reporting

Imagine being able to look at your data from every which way — from the bird’s eye view right down…

Power BI Hierarchy: Unlocking Levels and Drill-Downs in Visualizations

As you explore Python and take on more complex projects, you’ll notice that some projects have lots of files. Managing…

os.path.join: Simplifying File Path Operations in Python

Load More