Forum Discussion
Help with filtering blank rows when there is a second value
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
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.