Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Filter survey records where both Question 1 & Question 2 have a score of 10

Hi everyone

I am in dire need of some help around calculating the total satisfaction score based on two conditions meeting a certain criteria where two questions in a survey (Q3 & Q4) need to have a score of 10.

So i have tried something like below but the result does not match the requirement where both questions - Q3 & Q4 need to have a score of 10 per survey to be counted as a satisfied customer.

CustomerSatifaction = COUNTROWS(
CALCULATETABLE('Survey Results',
FILTER(
'Survey Results','Survey Results'[Question_Score] =10
),
FILTER('Survey Results','Survey Results'[Question no] = 3 && [Question no] = 4 )
))
@

@

1 ACCEPTED SOLUTION
Super User

Here's a measure that works for both cases.

``````M1 =
var q3 = SELECTCOLUMNS(filter('Survey Results','Survey Results'[Question Score]=10 && 'Survey Results'[Question no]=3),"InteractionId",'Survey Results'[InteractionId])
var q4 = SELECTCOLUMNS(filter('Survey Results','Survey Results'[Question Score]=10 && 'Survey Results'[Question no]=4),"InteractionId",'Survey Results'[InteractionId])
var i = NATURALINNERJOIN(q3,q4)
return COUNTROWS(i)``````
18 REPLIES 18
Super User

Ah, I wasn't clear that you are checking two rows together. In that case just run two countrows

``````CustomerSatifaction = COUNTROWS(
CALCULATETABLE('Survey Results',
'Survey Results'[Question_Score] =10,'Survey Results'[Question no] = 3
)>0
&& COUNTROWS(
CALCULATETABLE('Survey Results',
'Survey Results'[Question_Score] =10,'Survey Results'[Question no] = 4
)>0``````
Anonymous
Not applicable

Hi @lbendlin , The syntax for that calcution is throwing up an error - "Multiple Columns Cannot be converted to a scalar value".
Brackets may be one of the issues.

cheers

Chris

Super User

Yes, did you correct the brackets?

``````CustomerSatifaction = COUNTROWS(
CALCULATETABLE('Survey Results',
'Survey Results'[Question_Score] =10,'Survey Results'[Question no] = 3
))>0
&& COUNTROWS(
CALCULATETABLE('Survey Results',
'Survey Results'[Question_Score] =10,'Survey Results'[Question no] = 4
))>0``````
Anonymous
Not applicable

Hi @lbendlin , i did do that & it provides me with a True/false result which is not what i want.

I want a count of interactions where both Questions have a score of 10.

cheers

Chris

Super User

Then just convert the true/false to 1/0

CustomerSatifaction = if(COUNTROWS(
CALCULATETABLE('Survey Results',
'Survey Results'[Question Score] =10,'Survey Results'[Question no] = 3
))>0
&& COUNTROWS(
CALCULATETABLE('Survey Results',
'Survey Results'[Question Score] =10,'Survey Results'[Question no] = 4
))>0,1,0)

and then create a Total measure from that as needed. How do you visualize the data? Just the totals or do you show the InteractionId details too?
Super User

Here's a measure that works for both cases.

``````M1 =
var q3 = SELECTCOLUMNS(filter('Survey Results','Survey Results'[Question Score]=10 && 'Survey Results'[Question no]=3),"InteractionId",'Survey Results'[InteractionId])
var q4 = SELECTCOLUMNS(filter('Survey Results','Survey Results'[Question Score]=10 && 'Survey Results'[Question no]=4),"InteractionId",'Survey Results'[InteractionId])
var i = NATURALINNERJOIN(q3,q4)
return COUNTROWS(i)``````
Anonymous
Not applicable

Thanks @lbendlin , that's perfect!

Super User

@Anonymous

maybe you can try below DAX

``````CustomerSatifaction = COUNTROWS(
CALCULATETABLE('Survey Results',
FILTER(
'Survey Results',('Survey Results'[Question_Score] =10 &&'Survey Results'[Question no] = 3 )&&('Survey Results'[Question_Score] =10 &&'Survey Results'[Question no] = 4 )
)
)``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

@ryan_mayu   There needs to be an OR ("||") between the sets. Alternatively, use IN

``````CustomerSatifaction = COUNTROWS(
CALCULATETABLE('Survey Results',
'Survey Results'[Question_Score] =10 && 'Survey Results'[Question no] IN { 3,4 }
)``````

Anonymous
Not applicable

Hi @lbendlin , I have tried that expression amongst several other variations and it's not returning the correct result!
I want to Count the interactions where both conditions of the filter criteria are true I.e. Questions 3 and Question 4 have a score of 10.

If you see my table result in the above comment it has both questions having 10 as an answer. Your expression returns interaction where either Q3 or Q4 has a score of10 which is not correct.

This calculation is turning out to be a nightmare though it is so easy to write in sql or excel lol. thanks again 🙂

@Greg_Deckler pls join the party if you can 🙂

Anonymous
Not applicable

Hi @ryan_mayu

Thanks for your attempt but that expression gives me a blank.
it sounds simple in my head but i can't get the calculation to evaluate two filter conditions for a survey and only count if both conditions are met.

Do feel free to keep giving it a go, really appreciate your time 🙂

cheers

Chris

Super User

@Anonymous

could you please share the screenshot of raw data or sample file?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Anonymous
Not applicable

Thanks, pls see image attached.

 InteractionId Question no Question name Question Score 1001030255 3 AT CS Q3 10 1001030255 4 AT CS Q4 10 1001030292 3 AT CS Q3 10 1001030292 4 AT CS Q4 10 1001030523 4 AT CS Q4 10 1001030582 3 AT CS Q3 10 1001030628 3 AT CS Q3 10 1001030628 4 AT CS Q4 10 1001030946 3 AT CS Q3 10 1001030946 4 AT CS Q4 10 1001031082 3 AT CS Q3 10 1001031082 4 AT CS Q4 10 1001031454 3 AT CS Q3 10 1001031556 3 AT CS Q3 10 1001031556 4 AT CS Q4 10 1001032148 3 AT CS Q3 10 1001032148 4 AT CS Q4 10 1001032583 3 AT CS Q3 10 1001032583 4 AT CS Q4 10 1001033439 4 AT CS Q4 10 1001033738 3 AT CS Q3 10 1001033738 4 AT CS Q4 10 1001033962 3 AT CS Q3 10 1001033962 4 AT CS Q4 10 1001034069 3 AT CS Q3 10 1001034492 3 AT CS Q3 10 1001034492 4 AT CS Q4 10 1001035327 3 AT CS Q3 10 1001035327 4 AT CS Q4 10 1001035338 3 AT CS Q3 10 1001035338 4 AT CS Q4 10 1001035549 3 AT CS Q3 10 1001035549 4 AT CS Q4 10 1001036167 3 AT CS Q3 10 1001036167 4 AT CS Q4 10

Super User

Hi,

You may download the PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

@Anonymous

I tried this, but now sure if this is the best solution for you.

I will create a table

``````Table 2 =
ADDCOLUMNS(SUMMARIZE(question,question[InteractionId]),"Q3",MAXX(FILTER('question','question'[InteractionId]=EARLIER('question'[InteractionId])&&'question'[Question name]="AT CS Q3"&&'question'[Question Score]=10),question[Question Score]),"Q4",MAXX(FILTER('question','question'[InteractionId]=EARLIER('question'[InteractionId])&&'question'[Question name]="AT CS Q4"&&[Question Score]=10),question[Question Score]))``````

Then create a measure

``Measure = CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2','Table 2'[Q3]=10&&'Table 2'[Q4]=10))``

Hope this is helpful.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Anonymous
Not applicable

Thanks Ryan, Is it possible to avoid creating a Table as i have limited access & need to add it as a Measure only..

Super User

@Anonymous

You can try below DAX to create a measure directly.

``````RESULT1 =
var tbl=
ADDCOLUMNS(SUMMARIZE(question,question[InteractionId]),"Q3",MAXX(FILTER('question','question'[InteractionId]=EARLIER('question'[InteractionId])&&'question'[Question name]="AT CS Q3"&&'question'[Question Score]=10),question[Question Score]),"Q4",MAXX(FILTER('question','question'[InteractionId]=EARLIER('question'[InteractionId])&&'question'[Question name]="AT CS Q4"&&[Question Score]=10),question[Question Score]))
VAR TBL2= FILTER(tbl,[Q3]=10&&[Q4]=10)
RETURN COUNTROWS(TBL2)``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Anonymous
Not applicable

Thanks Ryan, Solution looks really promising! i just need to reconcile it back to ensure the totals are correct.

In the meantime is it possible to ignore blanks in both Q3 & Q4 columns when i through it up on a table visual or Dax studio. It just helps with the reconciliation. Cheers for all your help 🙂

End output:

 Date Interationid Question3 Question4 1/05/2020 00:00 1001652709 10 10 1/05/2020 00:00 1001650173 10 10 1/05/2020 00:00 1001651606 10 10 1/05/2020 00:00 1001651532 10 10 1/05/2020 00:00 1001652698 10 10 1/05/2020 00:00 1001650626 10 10 1/05/2020 00:00 1001651385 10 10 2/05/2020 00:00 1001653700 10 10 2/05/2020 00:00 1001653757 10 10 2/05/2020 00:00 1001653627 10 10 2/05/2020 00:00 1001653372 10 10 2/05/2020 00:00 1001653365 10 10 2/05/2020 00:00 1001653809 10 10 2/05/2020 00:00 1001653695 10 10 2/05/2020 00:00 1001653611 10 10 3/05/2020 00:00 1001654573 10 10 3/05/2020 00:00 1001654501 10 10 3/05/2020 00:00 1001654530 10 10 3/05/2020 00:00 1001654332 10 10 14/05/2020 00:00 1001691567 10 10 14/05/2020 00:00 1001691682 10 10

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors