cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nogarr Frequent Visitor
Frequent Visitor

Measure based on two slicers over the same column

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

ProjectCountryIndustry
Pro_1SpainAutomotive
Pro_2AustraliaAutomotive
Pro_3BrazilForestry
Pro_4UKCoal mining
Pro_5UKPlastics
Pro_6USMachinery
Pro_7SpainCoal mining
Pro_8UgandaPlastics
Pro_9MexicoMachinery
Pro_10USAutomotive

 

Table 2

ProjectAnswerScore
Pro_11-2
Pro_121
Pro_132
Pro_140
Pro_152
Pro_161
Pro_21-1
Pro_222
Pro_23-2
Pro_240
Pro_251
Pro_262
Pro_312
Pro_321
Pro_332
Pro_341
Pro_35-1
Pro_36-2
Pro_412
Pro_422
Pro_431
Pro_442
Pro_45-2
Pro_46-2
Pro_51-2
Pro_521
Pro_532
Pro_540
Pro_552
Pro_561
Pro_61-1
Pro_622
Pro_63-2
Pro_640
Pro_651
Pro_662
Pro_712
Pro_721
Pro_732
Pro_741
Pro_75-1
Pro_76-2
Pro_812
Pro_822
Pro_831
Pro_842
Pro_85-2
Pro_86-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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Measure based on two slicers over the same column

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])

Capture7.JPG

 

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])))

Capture8.JPG

 

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.

View solution in original post

4 REPLIES 4
Washivale Member
Member

Re: Measure based on two slicers over the same column

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

 

 

nogarr Frequent Visitor
Frequent Visitor

Re: Measure based on two slicers over the same column

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).

 

Community Support Team
Community Support Team

Re: Measure based on two slicers over the same column

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])

Capture7.JPG

 

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])))

Capture8.JPG

 

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.

View solution in original post

nogarr Frequent Visitor
Frequent Visitor

Re: Measure based on two slicers over the same column

Thank you, that did help!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 180 members 2,300 guests
Please welcome our newest community members: