Filtering a table based on different columns

[EnterThaBoyd]

Hello! I'm trying to make a report/visual aid to assist in personnel training. They have been tracking everything in excel, and want a better way to show bosses how good or bad we are doing in complying. 

Here is what my data looks like:

Personnel	Training1	Training 2	Training 3
John Doe	1/1/2024	1/2/2023	1/3/2022
Jane Doe	1/1/2024	1/2/2024	8/8/2024

The table went wonky, i can rewrite if it doesnt make sense

(For simplicity of example: assume training is annual, so John would be overdue for Training 2&3)

So far I already have calculation columns and measures to help me with some donut graphs (x% g2g, y% overdue, that kind of thing.)

# Column
IsTraining1Current = IF('dataset'[Training 1] >= TODAY() - 365, 1, 0)

# Measure
Remainder Training 1 = COUNT('dataset'[Personnel ]) - SUM('dataset'[IsTraining1Current])

What I would love to do is be able to select each training in a slicer, and then have a single column of personnel who still need to get their training done. 

So for example if I select "Training 3" in a slicer, it would filter the above table and only display "John Doe", since his training is over a year old. 

If anyone could at least point me in the right direction, that would be wonderful.

[Ritaf1983]

Hi @EnterThaBoyd 
You can apply these steps to achieve the desired goal :
1. unpivot the table from PQ:

2. Rename the columns to the normal names 🙂

3. After closing and applying create a measure :

Training test = if (max('Table'[Date])<=TODAY()-365,1,0)

4. Create the slicer for the training and the wanted table + filter the table by the test measure 

Result :

The pbix with the example is attached

If previous post helped, then please consider Accepting it as the solution to help the other members find it more quickly

[EnterThaBoyd]

I love this, but I was just given a change in the data and this no longer works. They added in "Status" (Full/Parttime/Contractor) and "Office" (Accounting, IT, etc). 

Personnel	Status	Office	Training1	Training2
John Doe	Full-time	IT	1/1/2024	1/2/2023
Jane Doe	Contractor	Accounting	1/3/2022	1/4/2024

So now if I try this, I will have 2 random fields that I don't want in the slicer.

[Ritaf1983 · Accepted solution]

Hi @EnterThaBoyd 
If you add columns you should select all of them except for training dates before unpivot.

The updated pbix is attached

If this post helped, please consider Accepting it as the solution to help the other members find it more quickly