Forum Discussion
Help with filtering blank rows when there is a second value
Hi
I have a matrix visual for employees and training completed. I am trying to filter out where the employee does not have the training.
The issue i have is that all of the training is contained within 1 column in the data so when i filter on does not contain in the filter it keeps all officers as they have training in other areas. I have created a new table which only contains the training i am interested in using select columns and filter this now however brings a blank row for everyone so again the filter on the visual does not work.
| Employee | Training |
| 1 | |
| 1 | Data Entry |
| 1 | Initial |
| 2 | |
| 3 |
So in the above example employee 2 and 3 have not completed the training but employee 1 has, I want to be able to just see the employees that have not completed the training.
I would restructure the data so that you have 3 tables :
- Employee, contains a row for every employee
- Training, contains a row for every training course
- Bridge, contains a row for every combination of employee & training course where the employee has completed the training
Create one-to-many relationships from Employee -> Bridge and Training Course -> Bridge.
You could then create a visual showing Employee[Name], create a slicer for training course and put a filter on the table / matrix to only show rows where Bridge[Employee] is blank.
Hi,
Write this measure and drag it to the Filter o n this visual group in the Filter pane. Apply a condition of equals 1
Measure = 1*(countblank(Data[Training])=countrows(Data))
Hope this helps.
7 Replies
Hi maurcoll
The question is: how do you determine whether an employee has completed the required training based only on the list of trainings they've completed? In your example, you say Employee 1 has completed the training, but what criteria are you using to determine that? Is there a master list of required trainings that you're comparing against? Also, Employee 1 has a blank row—does that indicate they're still missing one required training?
Hi
I have a table that lists all employees and the training they have completed. All the training they have completed is in one column with a date column indicating when it was completed. There isnt a master list of all required training. I then have the employee table which is all employees. I am only looking for a specific type of training and list of the employees who have and who have not completed it
Hi maurcoll , Thanks for the clarification. Based on your description, it doesn't sound like you need to restructure your model for this particular requirement. Since you already have an Employees table and a Training table containing one row per completed training, this should be achievable with a DAX measure that checks whether the current employee has a record for the specific training you're interested in.
I would restructure the data so that you have 3 tables :
- Employee, contains a row for every employee
- Training, contains a row for every training course
- Bridge, contains a row for every combination of employee & training course where the employee has completed the training
Create one-to-many relationships from Employee -> Bridge and Training Course -> Bridge.
You could then create a visual showing Employee[Name], create a slicer for training course and put a filter on the table / matrix to only show rows where Bridge[Employee] is blank.
Hi,
Write this measure and drag it to the Filter o n this visual group in the Filter pane. Apply a condition of equals 1
Measure = 1*(countblank(Data[Training])=countrows(Data))
Hope this helps.
Hi maurcoll , Thank you for reaching out to the Microsoft Community Forum.
As danextian already asked, could you please provide a few more details about how you determine whether a training is considered completed? In particular, it would be helpful to know whether you're checking for a specific training or comparing against a master list of required trainings and what the blank Training values represent.
Hi maurcoll , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
