Add or delete visuals based on slicer selection in Power BI

Question

How can we add or delete visuals based on slicer selection in power bi? Suppose I have a slicer to select 'State'. Once user selects a state value, the cities of those states should appear as cards with details like population, temperature etc. The cards should correspond to the number of the count of the cities in that state. I cannot hide/show the visuals as I need to manually add around 500 such visuals and manually change their properties everytime there is a change.

Showing a variable number of visuals doesn't seem feasible in this situation without tons of repetitive work. Is there a reason you can't use a multi-row card or a table/matrix visual instead of a bunch of separate cards? — Alexis Olson
The user wants a design where all sub items r shown as different cards. Also, As the number is huge, they want something like a linear view of all sub items. — HappieFeet

Texie Texie · Accepted Answer · 2020-11-05T10:27:53

I have a solution that I use but I'd not imagine it being feasible to use for so many different visuals (500 as you say), I use it for 20 and it works great.

You will have to work with the following:

A control table
A Switch measure that selects the other measures
A measure PER city
A Slicer and bookmarks

Create 1 visual with the population, temperature etc. Then instead of having the source values in there, create a measure per city. After which you can use a Switch True statement to have the values in the visuals change according to the users' selection.

Basically you then create a control table such as:

   ID     City_Name
    1     London
    2     Paris
    3     New York

After which you then create a new TOP measure: (on top of the others you created per city)

    Selected City= 
Switch(
    SELECTEDVALUE('Control Table'[Number_ID])
    , 1, [London Measure]
    , 2, [Paris Measure]
    , 3, [New York Measure]
    , [Default city without selection]
)

This is the measure that will be added as value to your Visual and will change accordingly.

Once you have your Control Table and measures ready the tedious part starts. You use a Chiclet Slicer with the ID Column of your control table as the value. Then you create a bookmark per City where only the selected value changes.

In this slicer when "1" is selected, save it as bookmark 1 London. The control table will ensure the correct measure is used from above Selected City Measure.

Do this for each city, then hide the slicer in the end.

Now you will have a single visual that changes according to the users' selection through bookmarks and the selected city calculation (switch statement) without having to create 500 visuals.

I suggest you try this first with 3 cities and see if it's a suitable solution.

Add or delete visuals based on slicer selection in Power BI

1 Answers