Filtering a slicer's values based on the values of another table

[Farhad88]

Hi everybody!

My goal is to filter the values of a slicer, based on the values selected from another table.

Problem description:

I have a slicer which shows the values of a dynamic parameter (named Y1-Axis Params) as the following:

The dynamic parameter (named Y1-Axis Params) is defined as the following.  So, in the table view, you should see three columns as the following:

And, I have another table (named: Table_Params_List) which contains the Id and name of some parameters, as the following:

I want to filter the slicer’s values based on the selected values from this table, so:

1- first I defined a measure to return the Id of the selected value:

Selected_ParamId = SELECTEDVALUE(Table_Params_List[Id],0)

2- then, I used a custom column to save the Id of all selected values:

Selected_ParamIDs =

 var _selectedIDs= [Selected_ParamId]

 return

 _selectedIDs

So far so good. Now Selected_ParamIDs shows the IDs of all selected rows from Table_Params_List:

Now, I want to filter the slicer’s values based on the values of this custom column. For this purpose:

I defined a new measure (named: Include?) as the following:

Include? =

var _selectedIDs= VALUES(Table_Params_List[Selected_ParamIDs])

return

IF(

    MAX('Y1-Axis Params'[Y=Axis Params Order]) in _selectedIDs,

    1,   0)

And then, I added this measure (using dag and drop) to the Filters pan of the slicer visual and configed it as the following:

As you can see, in this way, I could get the IDs of the selected items from the table, but the slicer still shows all of the values!

** But, if I use a measure as the following, it works well and filters the values of the slicer!!

Include? =

IF(

    MAX('Y1-Axis Params'[Y=Axis Params Order]) >= 2 ,

    1,   0)

So why it doesn’t work when I try to use the variable _selectedIDs? (But it works if I use some fixed values for the comparison!)

Any advice is very appreciated!

Thanks and best regards!

Farhad

[Farhad88]

Dear Power BI experts,

@amitchandak @Anonymous 

This issue is still open 😑. I have included the pbix file with the problem description in previous posts.

Any solution or suggestion is highly appreciated! 🙂

Best regards,

Farhad

[Anonymous]

Hi, @Farhad88 

Perhaps you can create a new table that will be based on the selected values in the Table_Params_List

Selected_Params =

CALCULATETABLE(

    FILTER(Table_Params_List,[Selected_ParamIDs]=1))

Modify the measure Include? to use the newly created table to determine whether a value should be included in the slicer.

Include? =

VAR _selectedParamOrder = MAX('Y1-Axis Params'[Y=Axis Params Order])

RETURN

IF(_selectedParamOrder IN VALUES(Selected_Params[Id]),1,0)

Apply the measure as a filter to the slicer.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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

[Farhad88]

Dear @Anonymous,

Many thanks for your reply. But it dosen't work unfortunately. 

I have provided more details, also the pbix files, in the following two posts (I don't duplicate them here just to prevent overcrowding the topic).

I look forward to hearing your further suggestions!

Best regards,

Farhad 

Dynamic Slicer.pbix

Dynamic Slicer 2.pbix

[amitchandak]

@Farhad88 , measure seem good, Try this version and check for not blank

countrows(intersect( Values('Y1-Axis Params'[Y=Axis Params Order]),VALUES(Table_Params_List[Selected_ParamIDs])))

[Farhad88]

Dear @amitchandak and @Anonymous,

Thanks for your suggections.

I tried them out, but they were not promising, unfortunately.

As I showed in the following screenshot, I can get the Id of the selected values from the table, but when I try to filter the slicer based on these IDs (using the "Include?" measure) it doesn't work:

I also attached the Dynamic Slicer.pbix file for further investigations.

Thanks and best regards,

Farhad

[Farhad88]

I noticed that if we convert the slicer to a table visual everything works well! (screenshot and pbix file attached)

So, why the same code and settings work differently for slicer and table visuals?

And, more importantly, the same question remines: 

How we can filter the slicer's values dynamically based on the selected values of another table?

Dynamic Slicer 2.pbix

Thanks for your advice!