The problem of blank values being shown in a Power BI slicer is fairly well known, but did you know that in some circumstances slicers can show other values that you would not expect to see? While there’s a good reason for this it can nevertheless be mildly confusing for developers and end users alike when it happens.
Let’s take a look at one way this can happen. Here’s an M query that returns a table with up to five random numbers in:
[sourcecode language='text' padlinenumbers='true']
let
Source =
{1..5},
RandomNumbers =
List.Transform(
Source,
each
Number.Round(
Number.RandomBetween(
_,
_+100
)
, 0)
),
RemoveDuplicates =
List.Distinct(RandomNumbers),
ToTable =
Table.FromList(
RemoveDuplicates,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
ChangedType =
Table.TransformColumnTypes(
ToTable,
{{"Column1", Int64.Type}}
)
in
ChangedType
[/sourcecode]
Here’s an example of the output:
The key point to notice is that every time this query refreshes, a potentially different set of numbers will be returned.
Now consider a report page built from this table with three visuals on it:
There is a slicer on the left with five items in it, a table showing the actual contents of the table (I’ve disabled visual interactions so the slicer doesn’t slice the table) with five rows and a card showing the output of the following measure:
[sourcecode language='text' ] Selected Number = SELECTEDVALUE(MyNumbers[Column1], "Nothing Selected") [/sourcecode]
In the screenshot above you can see I have selected the value 78 in the slicer and the measure – as you would expect – displays that value.
Now what happens when you refresh the dataset and the table contains a different set of numbers? Here’s what:
Summarising what is shown here:
- After the refresh has taken place the number 78 no longer appears in the underlying table, which contains five rows still
- However, because 78 was selected before refresh took place, it still appears – and is still selected in – the slicer as an extra value meaning there are now six items visible in the slicer
- Even though 78 appears and is selected, the measure returns “Nothing Selected” because the DAX SelectedValue() function has detected that no single value has been selected
- As soon as another value in the slicer is selected (in this case 83), the value 78 disappears from the slicer
Something similar may occur when using the Sync Slicers functionality to synchronise two slicers with different source columns that contain different values (as described here). For example, say you have a second table created as a DAX calculated table with the following expression:
[sourcecode language='text' ]
MySecondTable = ROW("Column2",-1)
[/sourcecode]
If you create a slicer from this table, use Sync Slicers to synchronise it with the existing slicer on the report shown above:
…and then select the value –1 in the new slicer, you’ll see it results in the value –1 appearing as selected in the original slicer even though that value does not appear in the underlying column:
Once again, if you select something else in the slicer, the –1 value will disappear.
In my opinion this behaviour makes sense and shouldn’t be changed. In any case, in the real world I always try to avoid situations where the values in a slicer might disappear: if you have a dimensional model and are building slicers from dimension tables rather than fact tables, you should find that values are rarely removed from dimension tables, if ever. That said I can’t help wondering if there are situations where this behaviour could be useful, for example in scenarios where you want to show an initial selection that displays no data but which can’t subsequently be reselected by an end user. As always, I’m interested in your thoughts and comments.
