Slicing charts based on selected and unselected values

[srikumarv]

Hi,

 I have two charts depicting information from same table. I also have a slicer for these charts. I want the charts to reflect data one based on the selected value in slicer and other chart to display data for all values apart from the one selected.

I have not been able to understand what Dax function to apply here but assume the logic would be to first the value selected which i can get as follows and have used 

AcitivitySelected = If ( HasOneValue(BROKER_LINK_ACTIVITY[ACTIVITY]) , Values (BROKER_LINK_ACTIVITY[ACTIVITY]) )

But not how to i apply this selected value and all other values apart from this selected value to the below two charts

Slicer value selected

[DoubleJ · Accepted solution]

Hi That's an interesting issue and I hope I understand your request correctly.... To make this happen you need a filter that does not directly depend on the table you are filtering.

I setup a sample with a table containg of 3 types of activities and some values:

1. Create a new table that consists of the unique Activity entries, I named it "ActivityFilter"
   
   
   
   

   ActivityFilter = SUMMARIZE(Activity,Activity[Activity])

   You will use this table for your slicer. Do not link it to your main Activity table!
   
   
2. Create a Measure "SelectedActivity" that determines which Activity has been selected:
   

   SelectedActivity = IF(
       HASONEFILTER(ActivityFilter[Activity]),
       VALUES(ActivityFilter[Activity]),
       BLANK()) 

3.  Create a Measure "IsSelectedActivity"
   

   IsSelectedActivity = IF(
   	HASONEFILTER(Activity[Activity]), 
   		IF(VALUES(Activity[Activity]) = [SelectedActivity],
   			"Yes",
   			"No")
   		,"No")

4. Create your slicer containing the values from the "ActivityFilter" table
5. Create your visuals, in my case 2 tables
6. For the first visual put a filter on the visual level where using "IsSelectedActivity" = "Yes"
7. Do the same for the second visual, but with the value "No"

In my sample this results to this

I hope this helps

JJ

[meid]

Hello,

I have the same issue. I tried @DoubleJ 's solution unfortunately it's not working for me. The card displays the slicer selection but the visuals do not show anything.  Can anyone help me?

In addition, I have trouble understanding how Power BI will filter the Activity table based on a slicer on ActivityFilter if both tables are not linked.

Thanks 

[srikumarv]

Hi,

This just works perfectly fine. 

I was trying to apply this to charts and summary Multi Card visual on the same page to show summary statistics of the same information it does not work. When i add the Activity and isSelectedActivity fields on the Visual Filter only it does not work for MultiRow card but works perfectly fine for Clusterd Column Chart.

I need to display the Activity and IsSelectedAcitivity in the card to get the correct values. 

Appreciate your help. You solution works absoultely fine though for the problem i posted. This is just an extension of it

[DoubleJ]

Glad I could help!

Feel free  to mark my answer as solution, I would appreciate that 🙂

[srikumarv]

Hi ,

Would be able to tell me the problem regarding the issue i am facing for muti row card. Thanks

[truch2]

@srikumarv this issue is a toughie! PowerBI measures are scalar so this method only works for a sinlge selection. I have tried a bunch of work arounds with no luck. If anyone else has an idea or solution it would be much appreciated!