Automatically set dependent slicer depending on user selection of another slicer

[Morkil]

I have two slicers: Region and Department

Departments sit under regions (hierarchy).

I'd like to set up the behaviour to automatically display the region within the slicer dropdown based off the users selection of the department.

e.g., Department "IT55" sits under Region "55", therefore if a user selects "IT55" then the region dropdown should automatically display "55".

Currently the filter works as expected, but does not display the specific region (i.e., will filter to the relevant related region, but will not display it within the dropdown).

Thank you,

Mic

[Anonymous · Accepted solution]

Hi  @Morkil ,

I created some data:

Sorry, only ALL appears in the first line of the Dropdown slicer. This may be a power bi design problem.

You can submit an idea for it at https://ideas.powerbi.com/forums and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.

Or do you consider the following options

1. You can turn it into a List form, which will display the Region information directly:

Create calculated table.

Table 2 =
DISTINCT('Table'[Department])

Create measure.

Flag =
var _select=
SELECTEDVALUE('Table 2'[Department])
var _selectcolumn=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Region]=MAX('Table'[Region])),"1",[Department])
return
IF(
    _select in _selectcolumn,1,0)

Place [Flag]in Filters, set is=1, apply filter.

Result:

2. Or set a custom title, after the slicer is selected, the corresponding Region will be displayed on the title.

Create measure：

Measure =
var _select=
SELECTEDVALUE('Table 2'[Department])
return
"Region :"&" "&
MAXX(FILTER(ALL('Table'),_select ='Table'[Department]),[Region])

Format  --  Slicer header – fx.

Result：

If you need pbix, please click here.

Test01.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Morkil , refer if my video on the same can help

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

[Morkil]

Hi @amitchandak 

Thank you for the reply.

Not quite what I was looking for though. 

The relationship between the departments and region tables exist already. I am trying to make it so that if a department is selected, the region dropdown is automatically set to what ever region the particular selected department belongs to.

E.g., Department "IT - California" is selected, so the Region "California" should be automatically displayed in the dropdown slicer. 

Currently this works, but the dropdown slicer dispalys "All". If you expand on the dropdown, it will only display the region the department belongs to - but I want to automatically set (display) this in the dropdown itself.

An example:

Here you can see that department "Mineral" is selected, yet region displays "All".

Yet if I expand upon the region dropdown slicer, it displays only the region that department "Mineral" belongs to

Instead of showing "All" I want an interaction to occur that if a particular Department is selected, then it's related region should be automatically displayed in the slicer dropdown - not "All".

Thank you