SELECTEDVALUE DAX Example- Harvesting Slicer Selection

In this tutorial, I’ll show you how to harvest or capture a value inside a measure to reuse in another measure and achieve dynamic calculations. I use a combination of measure branching techniques with SELECTEDVALUE DAX function in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

The Power BI team has made some updates to formulas recently that enable this to work better than before.

It’s not difficult to do, so let’s jump into it

Creating A Supporting Table

Here I have a really simple measure, which is a sum of my revenue (so just sales). But it could be anything, for example, it could be your quantity or your costs or it could just be any core measure that you create.

If all these increased by a selected amount, say 5%, 10%, or 15%, I want to show the impact of that. And I want to make it dynamic so that I can select different amounts, and then be able to see what the new scenario amount is.

The first thing we need to do to create this is to Enter Data. So, I’m going to enter some values here, and then we’re going to harvest a selection based on these values.

So, I’m going to call this Percent Change. And then I’m going to put some values in here, say 5%, 10%, 15, 20, and 30%. Iâ€™ll call this table Percentage Change. So, I’m basically creating a new table here.

Instead of getting it from Excel, I’ve just created it from scratch using the Enter Data feature embedded into Power BI desktop.

Once I load that in, this supporting table will come into my model, like so.

Inside our supporting table, I’m going to change the formatting to percentage and get rid of the decimal point.

Harvesting A Slicer Selection Using The SELECTEDVALUE DAX Function

In the report page, I’m going to create a slicer and make it a list.

Now, I want to be able to select one of these and be able to harvest that selected measure. I want to be able to input this measure and combine it with my Total Sales to see what the scenario sales would be.

We can’t do that at the moment because it’s just a column for now. We need to get it into a measure.

So, I’m going to create a new measure here, and I’ll call this % Change. Then, I’ll use the SELECTEDVALUE DAX function and grab my Percentage Change (Percent Change) column.

The alternative result is quite important here because if say nothing is selected, then you’re going to get an error if you don’t put an alternative result.

Sometimes, you can use BLANK, but that’s not going to help us in this case. If nothing is selected, we want to show that there’s a zero impact. So, I’ll place here zero (0) as the alternative result.

I just need to make sure this is formatted correctly as well. I’ll turn this into a percentage. And then I’ll drag this measure into the canvas and we’ll look at it as a card.

This is what I mean by harvesting. I can select from the slicer, say 10%, and I get 10% in the card visualization.

When I drag this measure into the table, you’ll see that I now have this 10% across every single line. I’m harvesting it by the selection, so it changes by the selection.

A fairly simple technique but you can apply this for more advanced analytics especially on scenario analysis and predictive analysis. I’ll show you how to create a simple dynamic calculation out of this SELECTEDVALUE DAX formula.

Creating A Dynamic Calculation Using The SELECTEDVALUE DAX Formula

Now Iâ€™ll create another measure and Iâ€™ll call this Scenario sale. And then I can go equals Total Sales multiplied by one (1), plus the percent change (%Change).

This is how you can create these dynamic calculations. Just think about ways that you can expand on this. You can have multiple different parameters impacting your scenario sales.

And that’s what you can achieve by harvesting your slicer selections. You’ll see that itâ€™s nothing difficult. Itâ€™s literally just recognizing that you can use the SELECTEDVALUE DAX function and then embedding the column that you created using Enter Data into the selected value parameter.

Conclusion

You might think that this is just so simple, but this is the beginning of some quite advanced analytics, especially around scenario analysis and predictive analysis.

This is how you can embed these values into your reports and get dynamic calculations that you may never have seen before.

I’ve gone into this quite a lot in other tutorial series. I’ve even created an entire module dedicated to scenario analysis at Enterprise DNA that takes this to a more advanced stage, so make sure you check that out too.

Cheers!

Sam

MultiIndex In Pandas For Multi-level Or Hierarchical Data

MultiIndex in Pandas is a multi-level or hierarchical object that allows you to select more than...

Power BI DAX ALL Function – How It Works

I want to give you a really quick introduction to the Power BI DAX ALL function. I find that there can...

Sales Vs Budgets Insights â€“ Extended Budget Allocation Formula

In this tutorial, Iâ€™m going to show you an extended version of the ultimate Budget Allocation...

This blog will show you some of the best tools and tricks to quickly create excellent visuals for your...

Power BI Percent Of Total – Using CALCULATE Statement

Power BI percent of the total is a really common calculation that we require quite often. This is great...

Power Query Data Types And Connectors

This tutorial will talk about Power Query data types and connectors. You'll learn what data types are...

Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power...

DAX CALCULATETABLE Vs FILTER Function

Both CALCULATETABLE and FILTER are powerful tools for manipulating and analyzing data in DAX query, but...

Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables

I'll show you how to set up dynamic start dates and end dates using Power BIâ€™s power query date...

Bookmarks In Power BI – Grouping by Report Page

I'm going to show you how I utilize bookmarks in Power BI. For example, if I have multiple bookmarks...

Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...

Creating A Master Reporting Planner For Power BI Deployment

In this tutorial, youâ€™ll learn how to create a Master Reporting Planner in the Analyst Hub to organize...