Power BI Antipatterns #2: SELECTEDVALUE

The pilot issue of the Power BI Antipatterns series received good feedback, and I’m back with the second blog post in the series, in which I’m going to discuss the SELECTEDVALUE function.

First, what does SELECTEDVALUE do? It’s a function that was introduced in 2017:

If there’s one value, SELECTEDVALUE will return that value, otherwise it’ll return some default value, or a blank if the second parameter isn’t used.

In this blog post, I’d like to discuss two ways to misuse SELECTEDVALUE.

Example 1

Here’s the first example:

Example 1 bad = 
IF(
    HASONEVALUE('Product'[Color]),
    SELECTEDVALUE('Product'[Color]),
    "All selected colors"
)

In case you don’t notice the problem, let’s expand SELECTEDVALUE:

Example 1 bad expanded = 
IF(
    HASONEVALUE('Product'[Color]),
    IF(
        HASONEVALUE('Product'[Color]),
        VALUES('Product'[Color])
    ),
    "All selected colors"
)

As you can see, we’re performing the HASONEVALUE check twice. Wouldn’t it be better to perform it only once?

Example 1 one check = 
IF(
    HASONEVALUE('Product'[Color]),
    VALUES('Product'[Color]),
    "All selected colors"
)

Written in this way, we can use SELECTEDVALUE instead, making the formula shorter:

Example 1 good = 
SELECTEDVALUE(
    'Product'[Color],
    "All selected colors"
)

An exception would be when you want to change the returned value — then you can’t really use SELECTEDVALUE:

Example 1 exception = 
IF(
    HASONEVALUE('Product'[Color]),
    VALUES('Product'[Color]) & " color",
    "All selected colors"
)

Example 2

The second example is a variation of the first one:

Example 2 bad = 
IF(
    HASONEVALUE('Date'[Date]),
    SELECTEDVALUE('Date'[Date]),
    LASTDATE('Date'[Date])
)

What is this formula doing? If there’s one date, return the date, otherwise return the last date. As we now know, the formula above can be rewritten in the following way:

Example 2 still bad = 
SELECTEDVALUE(
    'Date'[Date],
    LASTDATE('Date'[Date])
)

Why is this still bad? Think about what LASTDATE does: it scans the available dates and returns the last one, so it can handle cases when there’s more than one date available. Can’t we just use LASTDATE then?

Example 2 ok = LASTDATE('Date'[Date])

While this is passable, LASTDATE is a table function that returns a table with one column and one row; if needed — like in a measure — it can be implicitly converted to scalar value. In most cases, you won’t notice a difference, although sometimes you might get some performance degradation, as discussed in an article by SQLBI. Therefore, it’s best to use MAX instead:

Example 2 good = MAX('Date'[Date])

That’s all for today — hope you enjoyed!

Download the sample file: 2 SELECTEDVALUE.pbix

To be continued…