cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
benjaminlperry Frequent Visitor
Frequent Visitor

Filter Function Help

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter Function Help

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 )
    )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Filter Function Help

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.

 

Community Support Team
Community Support Team

Re: Filter Function Help

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 )
    )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors