SELECTEDVALUE-DAX-–-Harvesting-Slicer-Selection

SELECTEDVALUE DAX – Harvesting Slicer Selection

One comment

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 technique 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 have made some updates to formulas recently that enable this to work in a far more effective way than what had to be done previously.

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

Creating A Supporting Table

I have here a really simple measure, which is a sum of my revenue, so just sales. But it could be anything, it could be your quantity or your costs. 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 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.

selectedvalue dax

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.

selectedvalue dax

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.

selectedvalue dax

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 Sales. And then I can go equals Total Sales multiplied by one (1), plus the percent change (%Change).

selectedvalue dax

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 even got an entire course module at Enterprise DNA within our education platform that covers a scenario analysis, extending this to a very advanced stage. So make sure you check that out, too.

Cheers!

Sam

Membership Banne

Center of Excellence

***** Related Links *****
How To Harvest Power BI Slicer Selections To Use Within Other Measures
How To Date Harvest In Power BI Using DAX
Showcasing Multiple Selections In A Power BI Slicer

***** Related Course Modules *****
Mastering DAX Calculations
Advanced DAX Combinations 

DAX Formula Deep Dives

***** Related Support Forum Posts *****
Default Value for a Slicer
DAX measure – Sum Between Two Dates Selected in the Slicer
Hierarchical Slicer Selection Values to Card

For more harvest slicer selection support queries to review see here…..

1 comments on “SELECTEDVALUE DAX – Harvesting Slicer Selection”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.