Forum Discussion
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 hav...
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)
ryan_mayu
Super User
Super UserAnonymous
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)
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 |