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.
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.
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.
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.
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.
I find this behavior very annoying but not easy to solve.
My preference would be to show the first value if the selected value doesn’t exist
We have a solution that is going to be installed in different tenants with different data each time.
Assume a slicer on department, the department showing on open will come from some test data..
We also have slicers from static tables showing values like “Last 7 days” or “2 hours”
This values are localized for each tenant based on tenant locale
The unlocalized version will still show.
It is possible to force the slicer to refresh the value – but only if your goal is to have “Single select slicer”. It is possible to trigger the refresh on the slicer list, so it would select the first actual value on the list. To do that you have to:
1. in slicer visualizations section go to Format and open Selection controls
2. set “Single select” as “Off”
3. set “Show “Select all” option” as “On”
4. make sure the slicer default value got switched to “All”
5. set “Single select” back as “On”
6. make sure the the slicer default value is still as “All” (fortunately didn’t refresh after switching Single select to On)
7. Done, save it. Refresh the data.
I’ve tried your steps but it didn’t helped. After step 5, you can’t see the ALL option. Because you cannot have single select and all option both at same time. Unless, I’ve missed any if you can show your steps in action that will be really helpful.
For people interested solving the first issue with respect to the slicer, it is possible to use the ‘Chiclet Slicer’ with the ‘Forced selection’ option checked. This ensures that always picks the first value in the filter no matter what.
Thanks RJ1993, you made my day.
To force refresh of the slicer and make disappear the extra (undue) extra lines I have created a SUMMARIZE table with just the column(s) from the original table source that I want to appear.
And then I used the SUMMARIZED table as the base for the slicer on the report page. .
By doing this, when opening the report page no more the extra undue lines appear.
Apparently the SUMMARIZE forces and cleans the trash .
If you have the problem please try and tell me if the solution worked for You
It does not work. Nonexistent values still remain selected upon report reopen / refresh.