Filters to be AND instead of OR

[Anonymous]

We are looking to compare improvement in employee evaluations from year-to-year. However, we only want to compare employees that had evaluations for both years. 

For example, if we are looking at 2018 evaluations, we only want to compare employees that have both a 2018 and 2017 evaluation.

When I build a report, I can use the filters on the side to filter my visualizations for each year.

But if I apply the year to a page level filter and select 2017 and 2018, it shows me evaluations from 2017 OR 2018 when I want to only see employees with an evaluation from 2018 AND 2018.

I've included some simple example data in an excel sheet. The data sheet is the raw data. Each sheet after that shows the filtered data I'd like to see for each year. Here's the data.

This may be a power query solution, so I will post there as well.

[Greg_Deckler]

I would create a column that would flag TRUE if they had evaluations in both years and FALSE otherwise and then filter on that.

[Anonymous]

@Greg_Deckler ,

That's the direction I was heading. I'm having trouble finding the DAX expression that would essentially search for the employee ID and then see if it had previous year in anwhere in the year column.

Don't need you to write the expression for me, but any point in the right direction would be great.

Eric

[Greg_Deckler · Accepted solution]

I don't have an example of your data so I have no idea how things are related, etc. But the general concept is to use COUNTROWS on a FILTER of, you may need to use RELATEDTABLE, and perhaps even EARLIER for your employee name/id. Really difficult for me to tell you specifically without sample data, table relationships, etc.

So, in psuedo-code,
  VAR __2017Count = COUNTROWS(FILTER('EvaluationsTable','EvaluationsTable'[Year] = 2017 && 'EvaluationsTable'[EmployeeID] = 'PeopleTable'[EmployeeID])

  VAR __2018Count = COUNTROWS(FILTER('EvaluationsTable','EvaluationsTable'[Year] = 2018 && 'EvaluationsTable'[EmployeeID] = 'PeopleTable'[EmployeeID])

  RETURN __2017Count + __2018Count

if it is 2, then they had evaluations in both years. This assumes a "People" table with employee ids that relates to an "EvaluationsTable" with the evaluations.