SELECTEDVALUE Function is Power BI Filter Functions in DAX which returns the value when there is only one value in the specified column else it returns alternate value.
SYNTAX
SELECTEDVALUE(<column_name>[, <alternate_result>])
column_name is the name of an existing column, using standard DAX syntax. It cannot be an expression.
alternate_result The value returned when the context for column_name has been filtered down to zero or more than one distinct value. It is an optional, when value for alternate_result is not provided the default value is BLANK().
Lets look at an example of SELECTEDVALUE function.
Here we have a sample table named Sales as shown below.
Lets take this table data into table visual, also create a slicer to filter the data into table based on SalesId as shown below.
Lets see the SELECTEDVALUE function in action.
Basic Example of SELECTEDVALUE function
SELECTEDVALUE fuction takes one specifed column name, and if that column have one value then it returns the same otherwise it returns alternate value if specified else returns blank().
Lets create a measure named Quantity_Measure that uses SELECTEDVALUE function as showm below
Quanity_Measure = SELECTEDVALUE(Sales[Quantity])
Lets darg this measure into table visual next to Quantity column as shown below to see the behaviour of SELECTEDVALUE Function.
As you can see, SELECTEDVALUE function returns the Quantity value against each row that is same quantity value as defined in Quantity column but for Total it returns blank.
So you will be wondering why the value for TOTAL is returned as BLANK by SELECTEDVALUE Function.
The reason for not getting value for TOTAL is that against the TOTAL the filter context is not applied to Single value.
Lets understand this, for SalesId =1 the filter context is applied on SalesId value 1and for that only one single value is filtered in Quantity column, so the SELECTEDVALUE function gets that single quantity value and returns the same quantity value that is 500, In similar way it goes for each rows in table.
But for TOTAL, It will be calculated for all quantities so filter context is applied to all values due to this the value in column is not single so SELECTEDVALUE function returns the default value that is blank.
So basically, SELECTEDVALUE function returns the same single value if there is only one particular value in specified column.
Lets filter the record in table for one SalesId from Slicer, then observe the behaviour of SELECTEDVALUE function.
Once you filter the table records for any single salesid, Lets say salesId =3. You will see that this time Total value is returned by SELECTEDVALUE Function.
The reason is that, filter context that is applied on SalesId=3 filter the single quantity value in specifed quantity column so SELECTEDVALUE function returns the same value against the record and this time TOTAL will be calaulated only for single value so SELECTEDVALUE function returns the value for TOTAL as well.
What happens when you select multiple values in slicer
As you can see, when you filter records for multiple values,TOTAL is not returned by SELECTEDVALUES function reason is same that for TOTAL , the column is filtered for multiple distinct values so SELECTEDVALUES function returns blank.
Using Alternate value in SELECTEDVALUE Function
If you do not want to returns blank then you can provide alternate value to SELECTEDVALUE Function.
Lets modify the above DAX, and specify the alternate value as following.
Quanity_Measure = SELECTEDVALUE(Sales[Quantity], "Mulitple Values Provided" )
Lets take a look on table visual, and you will see now instead of blank value in TOTAL there is an alternate value that you provided.
Using SELECTEDVALUE function to get the single value selected in slicer
You can also use SELECTEDVALUE function to get the single value selected in slicer.
Here you can see, we have a slicer that contains the salesId, and one card visual which returns the quantity value using the following DAX Quanity_Measure .
Quanity_Measure = SELECTEDVALUE(Sales[Quantity])
Lets filter one value in slicer, and you will see SELECTEDVALUE function returns the quantity for single selected value in card visual.
You can also use SELECTEDVALUE Function, when you have two tables that do not have relationship.
As you can see, there are two tables SalesDetails and SalesList as shown below.
SalesDetails table having a SalesId and Amount while SalesList table contains only unique SalesId.
And there is no relationship between these two tables as you can see below.
Lets take a look on requirement where you have one slicer and card visual as shown below, slicer is loaded with SalesId that is fetched from SalesList table while card visual is displaying Total Sales Amount returned by DAX that using a SalesDetails table.
As there is no relationship between these two tables, so anything that you filter from slicer will not make any changes in DAX calculation.
You can see, for selected SalesId value that is 2 yet DAX calulation returns overall sales amount not for SalesId =2.
In this situtation you can use SELECTEDVALUE function to get the single selected value of SalesId in slicer and use that value in DAX to filter the SalesAmount based on that.
Following DAX calulation is being used to Get the Sales Amount.
TotalQty = SUM(SalesDetails[Amount])
Lets modify the DAX, and use SELECTEDVALUE Function to get the selected SalesId value in Slicer and check that value to filter the SalesAmount in DAX.
TotalQty = VAR getsalesId = SELECTEDVALUE ( SalesList[SalesID] ) RETURN CALCULATE ( SUM ( SalesDetails[Amount] ), SalesDetails[SalesID] = getsalesId )
Once you commit the DAX, Lets select salesid =2 again from slicer, and you will see that this time SalesAmount is returned only for SalesId = 2.
This is very useful sometime when you have such requirement, remember it will work only when single unqiue value is filter in column that is specified in SELECTEDVALUE function.
Filter Functions
13,428 total views, 5 views today