Fifth Day of Christmas: DAX SELECTEDVALUE()

 🎵 On the fifth day of Christmas, my true love gave to me... 

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Dynamic Titles

Today's post is going to look at how I used the SELECTEDVALUE function to make a dynamic title for Santa's list. As you can see in the image below, the title of the table visualization updates based on the selection - if the "Naughty" pie section is selected, the title is "Santa's Naughty List", if the "Nice" pie section is selected, the title is "Santa's Nice List". If nothing is selected, it simply says "Santa's List". 

Nice and simple, but really impactful and helps your data storytelling reach the end user more effectively.

DAX function: SELECTEDVALUE()

SELECTEDVALUE is not an original DAX function, and so I had gotten used to doing things the long way without it. Now I use it all the time! So much so, that I've decided to introduce it in this 12 Days of Christmas series before its predecessor (aka the long way); HASONEVALUE(). 

SELECTEDVALUE() has two arguments:

1. Column Name (required)
2. Alternate Result (optional)

I must say I'm pretty pleased with the name Microsoft chose for this one - it does exactly what it says. SELECTEDVALUE function returns the current selected value of the column. Since we're talking about DAX, this of course considers the context of your report and data model. 

To see how this function works, I am going to create a fun little measure that uses the SELECTEDVALUE function to return a different title depending on what the users selects in my report: 

SantaListTitle =
"Santa's " & SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ) & " List"

Ultimately, I want this to update the title of my list visual as in the GIF above, but to see its behavior I am going to place it in a table visualization next to the 'Santa List'[Naughty or Nice?] column that I have used within the function:

Note I have left the second argument blank, and so nothing is returned in the total row. This makes my title easy to read. I could update this to say something like: 

SantaListTitle =
"Santa's " & SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] , "Entire") & " List"

Note how the total row now displays "Santa's Entire List", when nothing is selected in the  'Santa List'[Naughty or Nice?] column. 

How to:

1. Open the SantaList.pbix that we've been working on. 
2. From the report view, click New Measure.
3. Create the SantaListTitle measure from above (choose the one you prefer or adapt for your own use).
4. Click the + to add a new page to your report. 
5. Rename the page "Summary".
6. Tick [Name] and [Naughty or Nice] from your table to create a table visualization.
7. Click the Format Paint roller icon.
8. Scroll down to find the 'Title' heading - Turn it on. 
9. Click the fx icon next to Title Text.
10. In the Title Text conditional formatting pop up box, select:
1. Format by: Field Value
2. Based on field: SantaListTitle
11. Click OK. 
12. Add a pie chart to the page with:
1. [Naughty or Nice] in Legend
2. Count of [Name] in Values
13. Select Naughty from the pie chart and watch the table title update.

We'll continue working with SELECTEDVALUE function in this report throughout the 12 Days of Christmas series. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll look at SELECTEDVALUE function's predecessor HASONEVALUE and discuss why it still has value over SELECTEDVALUE.