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