Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 )
))
@
@
Solved! Go to 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)
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
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
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
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
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 )
)
)
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 }
)
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 🙂
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?
Proud to be a Super User!
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 |
Hi,
You may download the PBI file from here.
Hope this helps.
@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.
Proud to be a Super User!
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)
Proud to be a Super User!
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
216 | |
89 | |
76 | |
67 | |
60 |