Forum Discussion
New column based on 3 others
I'm summarizing the results of a survey and need to exclude results from part of the population.
Considering the example table below, I'd like to create a column in the table which will indicate that an employee's answers should be excluded from the analysis if they did not answer a, b and c at least once(no matter what question). In the below, only John's answers would be considered valid.
How can I create this column 'Valid' keeping in mind that I have 15k unique employees.
| Employee | Question | Answer | Valid |
| Anna | 1 | a | no |
| Anna | 2 | a | no |
| Anna | 3 | a | no |
| Anna | 4 | a | no |
| Anna | 5 | b | no |
| Anna | 6 | a | no |
| John | 1 | a | yes |
| John | 2 | b | yes |
| John | 3 | c | yes |
| John | 4 | c | yes |
| John | 5 | c | yes |
| John | 6 | c | yes |
| Blake | 1 | c | no |
| Blake | 2 | b | no |
| Blake | 3 | c | no |
| Blake | 4 | b | no |
| Blake | 5 | c | no |
| Blake | 6 | b | no |
Hi daniellf ,
If the "a,b,c" means all the answer, we can use following DAX to meet your requirement.
V = VAR e = [Employee] VAR result = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( Table1, 'Table1'[Employee] = e ), "A", [Answer] ) ) ) RETURN IF ( result = 3, "yes", "no" )number 3 means the answer number
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- SteveCampbell
Memorable Member
Valid = VAR _AnswerString = "a,b,c" VAR _emplID = SELECTEDVALUE ( 'YourTable'[Employee] ) RETURN IF ( CONCATENATEX ( CALCULATETABLE (VALUES ( 'YourTable'[Answer] ), 'YourTable'[Employee] = _emplID), 'YourTable'[Answer],"," )
= _AnswerString, "Yes","No" )You can change the VAR _AnswerString = "" to be a list of accepted answers, separated by commas
Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me! 
v-lid-msftCommunity Support
Hi daniellf ,
If the "a,b,c" means all the answer, we can use following DAX to meet your requirement.
V = VAR e = [Employee] VAR result = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( Table1, 'Table1'[Employee] = e ), "A", [Answer] ) ) ) RETURN IF ( result = 3, "yes", "no" )number 3 means the answer number
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
