How to filter a slicer based on other slicers

[bmms]

Hello everybody,

I am not able to filter a slicer based on other slicers.

In my example, I have the year and month slicers (calendar table) where I show a summary of registrys (registry table) and errors (error table) by cost center.

In addiction, i have other Cost Center slicer (cost center table), but I can't get this slicer to only select cost center from the year and month that was previously selected.

I turned on the interaction of the slicer, but the problem continues!

How can I solve this problem?

You can download my example using the follow link:

https://we.tl/t-weNH8ySp25

Best regards.

[Ashish_Mathur · Accepted solution]

Hi,

Write this measure

Count = COUNTROWS(Errors)+COUNTROWS(Registry)

Select the Cost Centre slicer and drag the measure to the filter pane.  Specify the criteria there as "Not blank".

Hope this helps.

[v-yueyunzh-msft]

Hi , @bmms 

You can create a measure like this:

Slicer filter =

var _selectedvalue=SELECTEDVALUE('Cost Center'[Name])

var _filterednames=SELECTCOLUMNS(FILTER(ALL('Registry'),YEAR('Registry'[Date])=SELECTEDVALUE('CALENDAR'[Year])&&MONTH('Registry'[Date])=SELECTEDVALUE('CALENDAR'[Month])),"1",'Registry'[Cost Center])

return

IF(_selectedvalue in _filterednames,1,0)

Then you can place the newly created measure into the visual filter of this slicer "Cost Centere" name:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[bmms]

Hello,

thanks for your feedback.

Your solution almost works for me, but i have a special scenario.

With your solution, if i don't select any month (i want values from all months), in the slicer doesen't appear any cost center for selection.

How can i resolve this?

Best regards.

[v-yueyunzh-msft]

Hi , @bmms 

You cna try to use this dax to replace:

Slicer filter = 
var _selectedvalue=SELECTEDVALUE('Cost Center'[Name])
var _month= VALUES('CALENDAR'[Month])
var _filterednames=SELECTCOLUMNS(FILTER(ALL('Registry'),YEAR('Registry'[Date])=SELECTEDVALUE('CALENDAR'[Year])&&MONTH('Registry'[Date]) in _month),"1",'Registry'[Cost Center])
return
IF(_selectedvalue in _filterednames,1,0)

Then we can solve this issue:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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