- Ciao friends.
In this video I want to introduce
the SELECTEDVALUE function in DAX
and show you a couple of examples
where it proves to be useful.
SELECTEDVALUE is a useful and powerful function,
and once you learn how and when to use it
you will find a lot of different scenario
where it makes a lot of sense to use it.
But as with all the DAX function,
you need to understand exactly what it does
in order to use it properly.
Let's start by looking immediately
at the scenario where SELECTEDVALUE is useful.
Look at this report.
I'm showing the product name,
the sales amount and the product class
all in the same report.
Now, this visual is currently a table.
Being a table I can put columns coming from
the product table, like I did here,
I put the class and the product name,
anywhere in the report.
So the product class is here beside the sales amount.
What if I want to transform this table into a matrix?
I can do that.
I just go there and transform that into a matrix.
Now, the product class went on the columns.
I don't want there, I want it on the rows.
And look what happens.
Now the product class is below the product name.
And I don't want to see the product class
below the product name,
because there is always one value
for the product class given a product.
What I'd like to see is the product class in this area here.
The thing is I cannot show columns in that area.
In order to show values there what I need is a measure,
therefore what I need to do
is create a measure that computes the product class.
And I can do that.
I start by building a measure that I call product class.
And I can start just writing product class.
But you see where the problem is,
I cannot even write product class.
Intellisense does not let me create
or use the product class column,
so the class column in the product table.
The reason is, there is no row context right now.
And because there is no row context,
I cannot access the values of a column.
A measure is executed in a filter context
where a column does not have a value.
What I can do
instead of retrieving the value of one row only,
I can use the VALUES function
and retrieve the values of product class.
What I want, or what I hope,
is that because there is always
one product only in my visual,
VALUES returns a table with one row only
and the automatic conversion
of a table into a scalar value will happen.
Unfortunately, if I put that in the report
I obtain an error.
And if we look at the details of the error
it says that a table of multiple values was supplied
where a single value was expected.
Indeed, my measure worked fine for all of the rows,
but it did not work here at the grand total,
because row by row
there is only one value for the product class,
but at the grand total, there will be multiple.
So what I need to do is protect my code and say well,
IF HASONEVALUE product class,
so if there is only one value visible
in the current filter context for the product class,
then compute the values of the product class.
Otherwise, just return a blank.
And if I use the measure now,
you'll see that I obtained my result.
Now the product class is among the different measures
that I can use in a matrix.
The only drawback here
is that I'm showing the product class
even when there are no sales.
So I need to add a further level of protection
that says, IF NOT ISEMPTY sales,
then compute your result.
Otherwise, as we did earlier, a blank is just fine.
And now I have my nice result.
The product class is shown only when there are sales,
and it's time to get rid of the product class from the rows.
So I now have the product name, the sales amount
and the product class all in the matrix,
as if they were a table.
The only problem, or where SELECTEDVALUE comes in,
is that writing this code all the time is kind of boring.
You always need to remember to add IF HASONEVALUE
and then VALUES.
SELECTEDVALUE does exactly what the code
from row four to row seven does.
I can replace these with SELECTEDVALUE
of product class.
Now the code is shorter.
I can just hit Enter,
and as you see, nothing changes in the result.
SELECTEDVALUE does exactly what my IF HASONEVALUE
code did earlier.
Not only, SELECTEDVALUE also provides me a default value.
In case there are multiple values,
I can use whatever other value as a default.
And as soon as I hit Enter,
you see that this string will appear here
at the grand total,
where there are multiple values for the product class.
As you see, you can control what happens row-by-row,
and also what happens at the total level.
Now, for the product class,
let's get rid of those asterisks,
which are not that meaningful at all.
Now I have the product class.
I can use SELECTEDVALUE the same way
to obtain not only the product class
but a product category.
Let's look at where the product category is.
In this specific model I have a snowflake.
So I have sales that goes to product,
and product has a relationship with subcategory,
and finally with category.
So product category appears here.
And I'm slicing by the product name, which is here.
So the filter starts from product,
and I want to access the product category.
What happens if I use, in the same way, SELECTEDVALUE?
I can create a new measure,
let's call it product category,
that checks IF NOT ISEMPTY sales.
And we take the SELECTEDVALUE
of product category.
Now I have my measure, I can put it in the report,
and it always shows a blank.
Why that?
Well, the answer is here.
The filter starts from product,
but this relationship is many-to-one, and then many-to-one.
This tiny arrow is showing
the direction of the filter context,
and it shows that the filter context goes from
the one to the many side,
not from the many to the one side.
I could make this relationship bi-directional,
but that would be bad, you know,
bi-directional relationships are dangerous.
So another option, which is what I'm going to do here,
is to use a bi-directional relationship,
but activate them only for the duration of my calculation.
So I can use CALCULATE SELECTEDVALUE.
And then I use CROSSFILTER to move the relationship
between sales subcategory key.
No, sales product key.
Actually, no sales between product subcategory key.
I need more space.
And product subcategory, subcategory key,
we make this relationship bi-directional.
And then we need to do the same
for the relationship between subcategories and categories.
So we also use CROSSFILTER between...
between category key and subcategory,
and the category key in category,
product category key.
Oh, here.
These need to be bi-directional too.
Now my code is much longer, but I need to author it this way
if I want to access a column which is on the one side.
I can now hit Enter, and if I did everything correctly,
you now see that I have my product category visible here.
So again, SELECTEDVALUE is useful.
You only need to pay attention to make sure that there will
be only one value visible in the current filter context
on the table where you want to grab the value.
I want to show you another example
where SELECTEDVALUE proves useful.
And it is when you have reports with sales amounts,
like in this case, but you want to use a slicer
to control the scale of this number.
So right now I'm showing the value as it is.
But what I would like to see is,
show the value divided by 1,000 or 1 million,
and that is extremely useful when the values
become very large and you want to reduce their size
in order to make them more readable for users.
So I have a slicer here.
I have my sales amount that is not going to change,
but the sales by scale measure is going to change
and reflect the selection that I made with the slicer.
You see that if I use a thousand, this number
becomes smaller, and if I use a million,
this number become even smaller,
so they're easier to confront and to compare.
How does sales by scale work?
Well, sales by scale uses, again, SELECTEDVALUE.
It uses SELECTEDVALUE from the scale table.
Scale is a table that is used in the slicer,
it's just a table containing one, 1,000 and 1 million.
When I place a filter with the slicer,
that filter reduces the number of rows visible
on the scale table,
and SELECTEDVALUE returns a value
only when there is actually one value.
By default, I'm taking one,
meaning that if I do not make any selection,
my sales by scale show the same value as sales amount.
And the same happens if I do multiple selection,
because SELECTEDVALUE returns the default value
when either there are no values visible
or there are too many values visible.
If I want to produce an error
when there are multiple values visible,
or when there are no selection,
I can change my sales by scale,
and, for example, I can throw an error,
that says, "Select only one scale".
If I now hit,
I'm missing a parenthesis.
If I now hit Enter,
because there is no selection,
it's likely the model returns an error.
Indeed, you see that if I look at the details,
it says, "There was a calculation error.
"Select only one scale."
And when you select only one scale,
now the number becomes the right one.
Returning an error or choosing a default value,
well, that depends specifically on the calculation
that you want to do.
But as you have seen,
you can use SELECTEDVALUE to read values from slicers
and use those values inside your formulas
in order to change the behavior of your formulas
and of your code.
Now, as you have seen,
SELECTEDVALUE is a simple and powerful function.
You can use it in a lot of different scenarios.
If you are interested
in looking at more scenarios about SELECTEDVALUE,
make sure to go to SQLBI.com.
There you will find the article that shows multiple examples
and a more complete description of the code.
And you will also be able to download the file
in order to try by yourself,
play with DAX and learn more about
the SELECTEDVALUE function.
Enjoy DAX.