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

Reply
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

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)

View solution in original post

18 REPLIES 18
lbendlin
Super User
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

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

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?

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!

ryan_mayu
Super User
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!




@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

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

InteractionIdQuestion noQuestion nameQuestion Score
10010302553AT CS Q310
10010302554AT CS Q410
10010302923AT CS Q310
10010302924AT CS Q410
10010305234AT CS Q410
10010305823AT CS Q310
10010306283AT CS Q310
10010306284AT CS Q410
10010309463AT CS Q310
10010309464AT CS Q410
10010310823AT CS Q310
10010310824AT CS Q410
10010314543AT CS Q310
10010315563AT CS Q310
10010315564AT CS Q410
10010321483AT CS Q310
10010321484AT CS Q410
10010325833AT CS Q310
10010325834AT CS Q410
10010334394AT CS Q410
10010337383AT CS Q310
10010337384AT CS Q410
10010339623AT CS Q310
10010339624AT CS Q410
10010340693AT CS Q310
10010344923AT CS Q310
10010344924AT CS Q410
10010353273AT CS Q310
10010353274AT CS Q410
10010353383AT CS Q310
10010353384AT CS Q410
10010355493AT CS Q310
10010355494AT CS Q410
10010361673AT CS Q310
10010361674AT CS Q410

 

Hi,

You may download the PBI file from here.

Hope this helps.

Untitled.png


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

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

1.PNG

Then create a measure

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

 

2.PNG 

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

 

@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:0010016527091010
1/05/2020 00:0010016501731010
1/05/2020 00:0010016516061010
1/05/2020 00:0010016515321010
1/05/2020 00:0010016526981010
1/05/2020 00:0010016506261010
1/05/2020 00:0010016513851010
2/05/2020 00:0010016537001010
2/05/2020 00:0010016537571010
2/05/2020 00:0010016536271010
2/05/2020 00:0010016533721010
2/05/2020 00:0010016533651010
2/05/2020 00:0010016538091010
2/05/2020 00:0010016536951010
2/05/2020 00:0010016536111010
3/05/2020 00:0010016545731010
3/05/2020 00:0010016545011010
3/05/2020 00:0010016545301010
3/05/2020 00:0010016543321010
14/05/2020 00:0010016915671010
14/05/2020 00:0010016916821010



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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