Measure based on two slicers over the same column

[nogarr]

Hi there!

I am building a model to try to compare data within the same table according to two different combinations of slicers affecting the same columns.

My data (a simplified version) is structured in two tables, one by containing the projects and its characteristics, and another one with the answers to each project: 

Table 1

Project	Country	Industry
Pro_1	Spain	Automotive
Pro_2	Australia	Automotive
Pro_3	Brazil	Forestry
Pro_4	UK	Coal mining
Pro_5	UK	Plastics
Pro_6	US	Machinery
Pro_7	Spain	Coal mining
Pro_8	Uganda	Plastics
Pro_9	Mexico	Machinery
Pro_10	US	Automotive

Table 2

Project	Answer	Score
Pro_1	1	-2
Pro_1	2	1
Pro_1	3	2
Pro_1	4	0
Pro_1	5	2
Pro_1	6	1
Pro_2	1	-1
Pro_2	2	2
Pro_2	3	-2
Pro_2	4	0
Pro_2	5	1
Pro_2	6	2
Pro_3	1	2
Pro_3	2	1
Pro_3	3	2
Pro_3	4	1
Pro_3	5	-1
Pro_3	6	-2
Pro_4	1	2
Pro_4	2	2
Pro_4	3	1
Pro_4	4	2
Pro_4	5	-2
Pro_4	6	-2
Pro_5	1	-2
Pro_5	2	1
Pro_5	3	2
Pro_5	4	0
Pro_5	5	2
Pro_5	6	1
Pro_6	1	-1
Pro_6	2	2
Pro_6	3	-2
Pro_6	4	0
Pro_6	5	1
Pro_6	6	2
Pro_7	1	2
Pro_7	2	1
Pro_7	3	2
Pro_7	4	1
Pro_7	5	-1
Pro_7	6	-2
Pro_8	1	2
Pro_8	2	2
Pro_8	3	1
Pro_8	4	2
Pro_8	5	-2
Pro_8	6	-2

My goal is to compare the average of scores across questions and for specific questions for one specific project, which would be selected using a slicer with that purpose (e.g: Pro_1) with the same metrics of a benchmark defined by selecting a mix of project characteristics (by using slicers) (e.g: projects in machinery). So far I have been able to develop a measure calculating the desired metric for specific projects / project characteristics and also measures to compare the two. The measures would be as follows:

Project Score = calculate( average( Table_2[Score]),
containsrow({"Pro_1"},Table_1[Project]))

Benchmark Score = calculate( average( Table_2[Score]),
containsrow({"Machinery"},Table_1[Industry]))

Project to Benchmark = Project Score - Benchmark Score

However, the problem is that those measures are not dynamic, and you need to change the measures every time you want to choose a new project or benchmark. I cannot find a way to make the content of containsrow dynamic defined by slicers.

Do you know any way to do this / workarounds using other systems to get to the desired outcome?

Thank you in advance for your help!

[v-juanli-msft · Accepted solution]

Hi @nogarr 

If i understand you correctly,

e.g:

there are two slicers-

Project->Pro_1,        Industry->machinery

Project Score->scores of Pro_1

Benchmark Score->scores of machinery

If so,

First create two table

industry = VALUES(Table_1[Industry])

project table = VALUES(Table_1[Project])

i create measures using "sum", you can create your measures by replacing "sum" with "average"

Project Score = calculate( SUM(Table_2[Score]),FILTER(Table_2,Table_2[Project]=SELECTEDVALUE('project table'[Project])))

Benchmark Score = calculate(SUM(Table_2[Score]),FILTER(Table_1,Table_1[Industry]=SELECTEDVALUE(industry[Industry])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nogarr]

Thank you, that did help!

[Washivale]

Hi @nogarr , Try exploring Hasonevalue() and SelectedValue() functions of DAX.

[nogarr]

Hi, @Washivale, unfortunately I have tried that before, but it does not work when you want to do a filter combining several options (e.g: filter in several industries / projects).