NEED HELP - Advanced Text Filtering (Multiple Columns)

[shelbyallen99]

Hi there, Power BI Community! 

I'm working with a team of engineers. Different engineers on the team are assigned to hundreds of on-going projects. To organize these projects (names, descriptions, due dates, sent dates, and which engineers are assigned) we recently created a database. I am in the process of creating a Power BI that pulls data from this database.

*I'm using DirectQuery and I'd like to keep it that way if possible.

MY QUESTION: How do I apply a custom "and" filter between columns?

I think I might need to either create a new measure, write a DAX query, create a calculated column, or a combination of all three. I've been running in circles with this for weeks and I can't figure it out.

I want to be able to filter for: if the Q3 Engineer equals "John Doe" OR the Q4 Engineer equals "John Doe" OR the Q4+ Engineer equals "John Doe"

*Q3 Engineer, Q4 Engineer, and Q4+ Engineer are 3 seperate columns.

Each project has 3 "reviewers:" Q3, Q4, and Q4+. This means that each project lists the names 3 different engineers.

Example A:

Project Name: SillyString SS

Q3 Engineer: John Doe

Q4 Engineer: Sarah Brown

Q4+ Engineer: Morgan Black

When this data is pulled into Power BI, Q3, Q4, and Q4+ are all seperate colomns. 

I have different table visuals in Power BI for each individual engineer. Each engineer's table shows their workload - all projects they are currently assigned to. Each project listed in an engineer's workload table is accompanied by the review they're assigned to (see Example A, above)

Standard filtering does not work for this task, and here's why:

John Doe is an experienced engineer who's capable of handling Q3, Q4, and Q4+ reviews. For the SillyString SS project, he is assigned as the Q3 Engineer. (This is the ONLY review he can complete for the SillyString SS project. Different engineers must complete the Q4 and Q4+.) John Doe is working on a total of 3 projects. In addition to the Q3 on SillyString SS, John Doe is the Q4 forTableTennis SS and the Q4+ for PingPong SS.

For John Doe's Workload Power BI table, I filtered by "John Doe" in the "Q3 Engineer" column. Only one project appeared: SillyString SS. TableTennis SS and PingPong SS did not appear in John Doe's Workload table because different engineers are listed as the Q3 on those projects. 

However, John Doe still has 3 on-going projects in his court. How can I show all projects for which John Doe is assigned as the Q3 Engineer, OR Q4 Engineer, OR the Q4+ Engineer?

REMEMBER: Q3 Engineer, Q4 Engineer, and Q4+ Engineer are 3 seperate columns.

What is the best solution to this problem? I am still a beginner in Power BI (or it sure feels like it.)

I think I might need to create a new measure, write a DAX query, create a calculated column, or a combination of all three. I've been running in circles with this for weeks and I can't seem to figure it out.

I want to be able to filter for: if the Q3 Engineer equals "John Doe" OR the Q4 Engineer equals "John Doe" OR the Q4+ Engineer equals "John Doe"

I appologize if this question is slightly confusing. I'm increcibly determined to solve this problem, so please leave a comment if you have any questions. 

[v-luwang-msft]

Hi @shelbyallen99 ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[amitchandak]

@shelbyallen99 ,  I would like the answer this one if that can help

I want to be able to filter for: if the Q3 Engineer equals "John Doe" OR the Q4 Engineer equals "John Doe" OR the Q4+ Engineer equals "John Doe"

if you do not need slicer

a measure  =

calculate(count(Table[COlumn]), filter(Table, [ Q3 Engineer] ="John Doe" || [Q4 Engineer] = "John Doe" || [Q4+ Engineer] = "John Doe" ) )

If you need slicer. Create one independent name table with all names. DO NOT join this with table

Then try like 

measure =

var _name = allselected(Name[Name])

return

calculate(count(Table[COlumn]), filter(Table, [ Q3 Engineer] in _name  " || [Q4 Engineer] in _name   || [Q4+ Engineer] in _name ) )