New column based on 3 others

[daniellf]

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

[v-lid-msft · Accepted solution]

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.

[SteveCampbell]

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!

Stay up to date on  
Read my blogs on