second slicer dynamically adapt to first slicer

[Anonymous]

Hello everyone,

I want dynamic interaction between two slicers. There are two possible ways to arrange the slicers. I would really appreciate any suggestion on how to achieve the first layout of the slicers. If that's not achievable then the second layout. If neither the first nor the second layout are achievable, I am open to other alternatives as long as they don't alter the requirement.

First layout:

The challenge is to adapt the second slicer options dynamically based on the first slicer selection where both the slicers have multi-select option. I should be having appropriate options in the second slicer independently for each option in the first slicer. In the image shown below, I have two slicers and a table: " division (slicer one) " , " covid_flag(slicer two) " and a "table" showing the different possibilities in the dataset for division and covid_flag combination.

Both the slicers come from the same master table (not shown in the images) and are just the two out of many columns in that master table which needs to be filtered based on these slicers. However these are not the only slicers filtering the master table. I believe that the interaction required between these two slicers (which this question is for) will not cause unwanted behavior of other slicers eventhough they might get appropriately filtered based on the selection made in these two slicers.

An example in both textual and visual format below: ( sorry for the long example)

Let's say that I select "LSG" in the division slicer, I should then be having only "covid" and "non-covid" option in the second slicer. Say, that I select "covid". Next when I select "LSI", I should be seeing "covid" and "non-covid" unselected options in the second slicer. Say, that I select both "covid" and "non-covid" for "LSI". Next I select "SDG" option which also has "Covid" and "Non Covid" options. Say, that I select "Non Covid", For one last selection, I select "CAD" in the division slicer, but since "CAD" doesn't have any options for "covid/non-covid", the second slicer should be blank.

This is how the desired output should look (from either the first layout or the second layout of the slicers):

Second layout:

The solution that I tried is creating hierarchical slicer with the first level as division and second level as covid_flag but then I don't want to see blank values under the division which dont have any value for covid_flag. You can refer to the table on the right in the first image to see which divisions have blank value OR the divisions slicer in the below image.

Please see the screenshot below for reference:

This is the sample dataset for your reference:

division	covid_flag
CAD
CMD
LSG	Covid
LSG	Non Covid
LSI	Covid
LSI	Non Covid
SDG	Covid
SDG	Non Covid
MSD

This is the sample desired output for your reference:

division	covid_flag
CAD
LSG	Covid
LSI	Covid
LSI	Non Covid
SDG	Non Covid

If the output desired is not possible to achieve from either of the layouts posted above, I am open to other solutions as long as the requirement is met. Thank you in advance for your suggestions.

[Anonymous · Accepted solution]

Thank you @SpartaBI for the quick response. Although the video that you suggested helped me a lot in understanding the effects of bi-directional cross filtering which I was having in my data model and wasn't even aware of. This helped me build a better data model. So thank you for that.

The article which helped me solve my question is:

https://radacad.com/removing-blanks-from-organizational-ragged-hierarchy-in-power-bi-matrix-visual

The solution presented in the link above talks about the second layout that I am referring to in my question and also helps remove blanks creating a ragged hierarchical slicer from a custom hierarchical slicer.

[SpartaBI]

@Anonymous this is the best practice technique for one slicer dynamically adapt to another one:
https://www.youtube.com/watch?v=oiCVBUQenls&t=11s
Hope that what you meant.

Showcase Report – Contoso By SpartaBI