Power BI – DAX Selected Value

By: David Rohlfs

Introduction

Have you ever wanted to have more control and dynamic ability over your DAX? Using the Selected Value function in DAX allows you to do this very well. When you use the Selected Value function, you are able to read the data that is present as a filter or a row and create options based on the True or False result of this function.

In this blog, we will go over when to use the Selected Value function in DAX with a couple examples from basic to complex so that you can see the full capabilities of this function. We will not be going through a walkthrough of this function as it is not a standalone function and is only meant to be used with other parent functions.

When to use it

You have probably seen me or someone else use the selected value function inside of their measures or calculated columns before. But have you ever thought about what it is really doing? I am going to talk through two main scenarios where the selected value function is used. The first will be inside of a measure, and the second will be within a calculated column.

Before we get into each scenario, let’s talk about the main purpose of the Selected Value function. When you are using a Selected Value function, you are trying to figure out if the current row has a value of “x”. This can be either on the table(s) that you are already working on, or on an entirely separate table that is somehow related to your calculation, like using a slicer that doesn’t have a relationship.

Inside of a Measure

Let’s go ahead and get to the more complex side of the Selected Value function by using a slicer that is not relationally connected to our fact table, but we will use DAX to let it create options for us. Here is the scenario:

We are working on creating a forecasting table and the end user wants there to be four different options that we choose from to give us a multiplication factor of 0.75, 1, 1.5, or 1.75. The way that we are going to do this is by using an outside table that can be sliced on by the end user to give us each of our options.

We have our Sales Multiplier table that contains only our slice-able column with each option as a text, and our sales fact table.

Next, we are going to build out our DAX. Here is my equation for the Sales with Multiplier measure:

Notice where my selected value statements are. I am using them at the start of the If statement so that I can go through and determine if my selected value is correlated to the function that I want to use.

Now let’s see what this looks like inside of my table. When I select a different option in my slicer, I am able to alter the amounts that are in my measure.

Inside of a Calculated Column

Since we have gone through using the Selected Value function with an outside table, let’s see how it works with finding data inside of a row. We are going to do a much simpler example where we are trying to find each row’s value when we filter down a table. I know that there are many ways to do this but think about just showing the value of a column with conditions tied to it.

We are going to go back to our sales fact table and create a calculated column that returns our city name if our sales are greater than $500 for that line. Here is my DAX for this equation.

Notice that I can use the Selected Value function to not only determine data that I want to filter by, but also put data back in that is on my specific row.

Conclusion

I know that I have heavily oversimplified the concept of the Selected Value function, but there are many instances where these examples can be used. If you want to see an example where the Selected Value function is vital and used in a very complex DAX equation, go read through the Dynamic Date Selection blog linked below.

 

Links Related to This Blog:

DAX If Statements

What if Parameter

All and All Except

Conditional Columns

Comments