Dynamic Filtering Based On Other Column Data

[SteveMciver]

Hello, I have a dataset structure similar to the below. My users need to be able select a variable in the report and have that variable compared to benchmarked data for variables of the same type - ideally in the same chart (though could be two charts side by side). 

I am struggling to find a way around the fact that once the page is filtered to the variable, the benchmark data is filtered out. I believe this could be overcome with a separate table, but that stops both the variable and benchmark data from being filtered by other data.

Here's a scenario for what I want to do using the dummy data:

- Select the Golden Delicious apple varitey from a table or slicer

- Have a chart update to show the 'Review Score' distribution for Golden Delicious vs the reveiw score distibution for all apples that are the same 'Type' (so green in this scenario) and 'Benchmark' = Yes.

- Filter the chart further to show the 'Review Score' distribution for both Golden Delicious  and the benchmarked 'Type' data for those under the age of 50 who live in the US. 

There are too many varieties to hard code the potential variables in the actual dataset.

Thanks for your help!

Tasted Apple UID	Apple Variety	Type	Review Score	Reviewer Age	Reviewer Country	Benchmark
121566	Granny Smith	Green	5	20	Germany	Yes
543543	Pink Lady	Red	3	45	US	Yes
57454541	Golden Delicious	Green	6	23	US	No
313245	Granny Smith	Green	4	67	France	Yes
543543	Red delicious	Red	1	18	Japan	No
545465	Pink Lady	Red	6	63	Brazil	Yes
21321	Granny Smith	Green	8	36	Mexico	Yes
54451	Golden Delicious	Red	4	32	UK	No
5454545	Red delicious	Red	3	50	France	No
475545	Red delicious	Red	5	41	UK	No
54545	Granny Smith	Green	1	34	UK	Yes
5454	Golden Delicious	Green	7	43	Germany	No

[v-angzheng-msft · Accepted solution]

Hi, @SteveMciver 

Based on what I got, you can follow the following steps.
Step 1: Create calculated table summary variety, type Benchamark fields, and create slicers with the summarized fields
Step 2: Try creating the following measures to filter the items.

Condition1 = 
var _Variety=SELECTEDVALUE(Variety_Type[Apple Variety])
var _type=SELECTEDVALUE(Variety_Type[Type])

var _currentVariety=MAX('Table'[Apple Variety])
var _currentType=MAX('Table'[Type])
var _currentBenchmark=MAX('Table'[Benchmark])

var _if=IF(_currentVariety=_Variety&&_currentType=_type,1,IF(_currentType=_type&&_currentBenchmark="Yes",1,0))

return _if

Condition2(further) = 
var _Variety=SELECTEDVALUE(Variety_Type[Apple Variety])
var _type=SELECTEDVALUE(Variety_Type[Type])
var _Benchmark=SELECTEDVALUE(Variety_Type[Benchmark])

var _currentVariety=MAX('Table'[Apple Variety])
var _currentType=MAX('Table'[Type])
var _currentBenchmark=MAX('Table'[Benchmark])

var _if=IF(_currentVariety=_Variety&&_currentBenchmark=_Benchmark&&MAX('Table'[Reviewer Age])=50&&MAX('Table'[Reviewer Country])="US",1,0)

return _if

Step 3: Then filter the items with measure equal to 1 in the filter pane.

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[MFelix]

Hi @SteveMciver ,

Not really sure If understand what you need but maybe you can use the interactions between the visualizations to get the values you need, so the apple variety only changes the values of a single chart (the one to benchmark) then create a slicer for each of the olther columns that will only interact with another chart, be aware to select the Benchmark also to filter.

Then just change the slicers and the second chart will change accordingly, if you want it to be in the same chart then this needs to be done with disconnected tables.

Chek PBIX file attach and please get back to me if this is not the expected result.

The chart on the left only interact with the slicer on the left and the ones on the right with the slicers on the right.