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"