Why Power BI Slicers Sometimes Show Values That Do Not Exist

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:

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

Here’s an example of the output:

image

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:

image

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:

Selected Number = 
SELECTEDVALUE(MyNumbers[Column1], "Nothing Selected")

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:

SlicerNewValues

 

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:

MySecondTable = ROW("Column2",-1)

image

If you create a slicer from this table, use Sync Slicers to synchronise it with the existing slicer on the report shown above:

image

…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:

image

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.

7 responses

  1. Pingback: When Values Disappear in Power BI – Curated SQL

  2. Pingback: Power BI Slicer histogram, dynamic dates, gateway update and more... (July 15, 2019) | Guy in a Cube

  3. Pingback: Why Power BI Slicers Sometimes Show Values That Do Not Exist | Pardaan.com

  4. This behavior makes sense but is extremely annoying if you have a snowflake design. Say you have a couple classes and each of those classes has its own selection of type and the report is a detailed analysis of a particular type. If you select a class and then a type, changing the class keeps the old, inappropriate type and filters out all the data until you select a proper type for that class. I think that this is a fairly standard and often necessary use case and the behavior of Power BI is weird and wrong here. You should be allowed to select a default value or clear the filter when its currently selected value disappears.

  5. I was with a client yesterday that saw this as a problem (a similar example at least). They have a drill through report that also operates as a stand alone report page. As a stand alone report, it has a single select slicer on the page – on the same column that acts as the drill through filter. When you drill through, the report is filtered for the drill through selection. Everything on the page is filtered including the single select slicer. But the single select filter still shows the previously selected value, just as you describe here. In this case there is a correct value that could be selected, but the slicer doesn’t change.

  6. I ran into this issue a couple of weeks ago and also yesterday and can confirm the authors impressions. In the 1st instance I have a report that I created that uses a disconnected table to get the start and end months from my date calendar. The query that creates the table takes all the month records from the date table and merges them with themselves. I have a Start slicer and a Finish slicer that are in synch with each other so that the dates cannot inadvertently be crossed (by selecting a finish date that is earlier then the start date). It works really well, however, when I was switching the queries from one project to the other I left the slicers on dates that did not exist in the first project. These dates were retained and essentially blanked all the visuals which had measures that used the selected values from the slicers. I think the only way I could fix it was to recreate the slicers. Yesterday I was amending this structure to add budget periods to one tab in the visual forgetting that the slicers were synced. In the visuals that only had actuals the slicer only shows the 3 dates where actuals existed, but if I went to the tab with budget data which had 12 months and selected a month with no actuals as the finish month and clicked to one of the actuals tab, the value showing was one that was being filtered from those tabs so I had to un-synch the budget slicers from the others.

  7. A possibly helpful workaround for default slicer values:
    We have run into this issue with sliding date windows that move with each refresh. We have Start Date and End Date slicers (via dummy date dimensions related to the main date table). They are required to default to the earliest and latest dates in the time window, but because of the behavior above they don’t change on refresh. Our workaround is to add calculated columns to the date dimensions that return the date value, except for the earliest and latest dates. In those cases it returns text values “start” and “end” respectively. Then the calculated columns are used in the slicers, with the “start” and “end” values selected when the report is published. On refresh, the slicer selections don’t change–but the dates they filter do! This works well for our use case but has the disadvantage that you can’t actually see the date in the slicer when you have “start” or “end” selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: