cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anant
Frequent Visitor

second slicer dynamically adapt to first slicer

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.

Anant_0-1653032336184.png

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):

Anant_1-1653032389141.png

 

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:

 

Anant_2-1653033753660.png

This is the sample dataset for your reference:

divisioncovid_flag
CAD 
CMD 
LSGCovid
LSGNon Covid
LSICovid
LSINon Covid
SDGCovid
SDGNon Covid
MSD 

 

This is the sample desired output for your reference:

divisioncovid_flag
CAD 
LSGCovid
LSICovid
LSINon Covid
SDGNon 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.

1 ACCEPTED SOLUTION
Anant
Frequent Visitor

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.

View solution in original post

2 REPLIES 2
Anant
Frequent Visitor

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
Community Champion
Community Champion

@Anant 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.
2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.