Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Automatically set dependent slicer depending on user selection of another slicer

  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 b...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Anonymous ,

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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