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.

Two PowerPivot Books

There’s no way I could ever pretend to be an impartial reviewer of Marco Russo and Alberto Ferrari’s new book, “PowerPivot for Excel 2010: Give Your Data Meaning”. First of all, they’re good friends of mine (we wrote “Expert Cube Development with SQL Server Analysis Services 2008” together last year); and secondly, I got a freebie copy of the book. But all that aside, I do honestly think it is a really good book. Just as an example, I’ve been playing around with some DAX problems over the last few days with a view to writing a some blog posts and had been struggling to get the EARLIER function to work in the way I’d been expecting; there are only a few examples of its use on the web but I found the book had a very detailed explanation of how it works. Indeed a large part of the book is concerned with DAX and it’s probably the best resource on that subject that I’ve seen, so that’s reason enough to buy it.

It’s important to point out, though, that Marco and Alberto’s book doesn’t really go into any detail on PowerPivot for Sharepoint – there is one chapter at the end, but it’s main focus is on PowerPivot for Excel. If you want to learn more about the Sharepoint side of things I can recommend “Professional PowerPivot for Excel and Sharepoint” by Siva Harinath, Ron Pihlgren and Denny Lee (and yes, I got this book as a freebie too – it’s one of the perks of being a blogger that you get loads of free books!). There’s very little overlap between the two books – the only thing they both cover is the basic info on how to build a PowerPivot model, and that subject is so basic most people will be able to work it out for themselves – so it’s probably worth getting both if you’re serious about learning PowerPivot.

One last bit of advertising: Marco and Alberto are running a two day PowerPivot workshop in the Netherlands at the beginning of December. More details can be found here:
http://www.sqlbi.com/workshop/

%d bloggers like this: