Select employees for which all status fields are "Completed"

[ericgerma]

Hello everbody,

I'm a new user of Power BI and this is my first question.

I'd like to filter a table structured like this : "Employee / Training title / Training status". Where Training status are "In progress" or "Completed".

I 'd like to only have the Employees for which ALL "Training titles" in the table are in status "Completed". If, for an employee, one or more status are "In progress", I don't want this employee in the table.

Could you help me with that ?

Thanks a lot 

[ericgerma]

@anandav : yes that's it 🙂 

@MarcoG2000 : thank's for this solution, i'll test it when i'll be at work 🙂

By the way, is there any solution to do this with DAX and to count thoses employees ?

My first idea was to do a measure to caluculate the % of completed trainings by users and filter only employees with 100%. But it did'nt work in DAX with that measure.

[anandav · Accepted solution]

@ericgerma,

 You can create a measure like below to use it in visual filter to achieve what you want.

To get the count of employees who have completed all teh training you can use a measure like:

Training Completed Count = DISTINCTCOUNT(Sheet1[Employee])-[Training Not Completed]

If this solves your problem please mark this as the solution.

[ericgerma]

@anandav Thanks; it works, with a little tweaks, but it works for me !

[MarcoG2000]

Hello, I posted a similar problem a few days ago (here is: synthetic result for each customer on each day). Your problem should be solved as mine was solved. Add a calculated column: in case the course is completed, put 0 else 1. If you use the "summarize" function and the "min" function all people who have zero have competed all the courses, otherwise someone has not completed at least one course.

[anandav]

@ericgerma,

Depending on your requirement you can do three things:

1. Use a Slicer to filter the data

2. Filter out 'In-Porgress' records in Power Query editor.

3. Filter out -In-Porgress' in visual or report filter settings.

1. Use a Slicer to filter the data

2. Filter out 'In-Porgress' records in Power Query editor

When you are loading the data select 'Edit' and go to Query Editor.

OR

https://docs.microsoft.com/en-us/power-bi/desktop-query-overview

3. Filter out -In-Porgress' in visual or report filter settings.

https://docs.microsoft.com/en-us/power-bi/power-bi-report-add-filter

[ericgerma]

Thanks a lot Anandav for your quick answer !

I give you more details about my case :

I have multiple trainings by users, not only one as in your exemple. So imagine Matt have T1 and T2 completed and Donna T2 in progress and T3 completed.

I only want to have Matt in my table because all of its trainings are completed but Donna not.

I hope to be clear in my explanations 🙂

[anandav]

@ericgerma,

What is the definition of "all of his trainings are completed"?

Do you want exclude any employee if any of the respective employee's training status is 'In-Progress' even if they have some tranining courses completed?