Initial value of Power BI slicer based on another slicer choice

Question

I would like to set up initial value of lower category slicer whenever upper category slicer value changes. Suppose we have this data:

+----------------+----------------+-------+
| upper category | lower category | units |
+----------------+----------------+-------+
| fruit          | apple          |     1 |
| fruit          | banana         |     1 |
| vegetable      | carrot         |     1 |
| vegetable      | potato         |     1 |
+----------------+----------------+-------+

We add two slicer visuals to the report. It looks like this:

My desired results are this: Whenever the user selects fruit from upper category, then apple is selected from lower category slicer. So we end up with this:

Now the we click on vegetable in upper category slicer, and automatically carrot is selected as an initial filter value of lower category slicer. So we end up with this:

Summary:

Upper category slicer shows all items, regardless of lower category slicer choice.
Lower category slicer shows only applicable items, based on upper category slicer choice.
After selecting item from upper category slicer, always one initial aplicalble value of lower category slicer is selected.

After googling I have found this as a promising clue to the solution: https://www.kasperonbi.com/embed-your-power-bi-report-with-predefined-filters/

I do not have to stick to slicer visual. It could be something else. What I want is to maintain this functionality. I could imagine that it could be accomplished with R visual by the concept expressed here: https://dataveld.com/2016/02/10/r-visuals-in-power-bi-beyond-plots/

Here is a start table code for your convenience:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSisqzSxR0lFKLCjISQXShkqxOgjRpMQ8IIQLl6Wmp5YkJoEVJicWFeWXYJUqyC9JLMmHSMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"upper category" = _t, #"lower category" = _t, units = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"upper category", type text}, {"lower category", type text}, {"units", Int64.Type}})
in
    #"Changed Type"

jeffreyweir · Accepted Answer

You can fake it by using the Bookmarks, but you need to effectively build a 'Parent' slicer using bookmark icons that sets the state of the other slicers

Here's how that looks:

That 'Parent' slicer at the top should be hidden.

Here's what happens if I click on the i icon under Fruit:

...and here's what happens if I click the other bookmark:

You can actually ditch the icons and have 'Fruit' and 'Vegetable' text instead. Here's how that looks:

Nothing selected:

Fruit clicked

Vegetables clicked

...although in reality you may need to space the fake Slicer Items further apart, as the title bar of the bottom one can cause issues with the interactivity of the top one, even though you can't see it. Here's a picture in Edit mode, so you know what I mean:

Furthermore, I find the 'Pin Visual' tip annoying, and there isn't (yet) a way to turn this off:

You could get fancier and have some kind of visual effect that somehow highlights which item in the fake slicer is selected. But the down side is that this approach doesn't allow you to handle dynamic lists of Slicer Items...you've got to set up each in advance.

Initial value of Power BI slicer based on another slicer choice

Answers (2)

Related Questions