Filter Function Help

[benjaminlperry]

So I have a table of survey responses and I am trying to create a calulated column for the whole table based on the survey response to a specific question by the related Employee.  Example of the table below:

Employee ID     Question ID     Question Response    

     1                       1                          Yes

     2                       1                           No

     1                      4                           a.albert

     2                      4                           h.hughes

     1                      7                         Strongly Agree

     2                      7                           Disagree

I want the new calculated column to show the question response to Question ID =4 (Who was your recruiter?) for the Related Employee ID.  So the it would look like this

Employee ID     Question ID     Question Response              Recruiter

     1                       1                          Yes                                  a.albert

     2                       1                           No                                  h.hughes

     1                      4                           a.albert                           a.albert

     2                      4                           h.hughes                         h.hughes

     1                      7                         Strongly Agree                  a.albert

     2                      7                           Disagree                          h.hughes

I assume I would need to use the Filter Function to Filter(Survey Responses, EALIER(empoyee ID) = employee ID && Question ID = Value(4)).  However, everything I have tried to this point has not worked. 

[v-frfei-msft · Accepted solution]

Hi @benjaminlperry ,

We can create a calculated column as below.

Column = 
VAR a = 'Table'[Employee ID]
RETURN
    CALCULATE (
        MAX ( 'Table'[Question Response] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Question ID] = 4 && 'Table'[Employee ID] = a )
    )

[Cmcmahan]

So this is more of a data modeling approach, but I would suggest adding this info to the Employees table instead of this survey table if you have to add it as a calculated column.

The better option would likely to be to create this as a measure. You can read more about calculated columns vs measures for beginners here. If you can assume that every Employee would only have one response to this question, you can use this measure:

Recruited_By = CALCULATE(SELECTEDVALUE(SurveyAnswers[Question Response]), FILTER(ALL(SurveyAnswers), [EmployeeID]=SELECTEDVALUE([Employee ID]) && [Question ID] = 4)

And then in any context where you only have one EmployeeID available, this measure will give you the appropriate recruiter.

If you absolutely have to have this as a calculated column, remove the first SELECTEDVALUE, and replace the second SELECTEDVALUE with EARLIER.