Forum Discussion

EnterThaBoyd's avatar
EnterThaBoyd
New Member
2 years ago
Solved

Filtering a table based on different columns

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/20241/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's avatar
    Ritaf1983
    2 years ago

    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

3 Replies

  • 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's avatar
      EnterThaBoyd
      New Member

      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). 

      PersonnelStatusOfficeTraining1Training2
      John DoeFull-timeIT1/1/20241/2/2023
      Jane DoeContractorAccounting1/3/20221/4/2024

       

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

      • Ritaf1983's avatar
        Ritaf1983
        Super User

        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