Forum Discussion

jojo14's avatar
jojo14
Frequent Visitor
8 years ago

Inverse filtering across tables, possible?

Hi all, 

 

I am fairly new to Power Bi. I have searched the internet, but I cannot seem to tell if this is even possible through power bi - here goes..

 

I am trying to map out which emlpoyee id's are missing certain trainings and who has already acquired certain trainings. I have 3 tables: number 1 is with employee information (Id's, name, title etc.), number 2 is the list of training that has been acquired by employees based on id's and number 3 is the unique list of trainings available. 

Now, it is straight forward to get the list of employees and training already acquried (as it exists in table number 2), but how do i get the opposite (all the uid's that have not concluded certain trainings)? 

Do i need some kind of inverse filtering? 

 

The ultimate goal is simply to have two dynamic tables: one that lists the people who have acquried training and one that list people who have not acquired training. Both have to filter when I choose a specific training in a visial or the like. 

 

I hope it makes sense, otherwise let me know!

 

Ps. I realize that this might already have been covered in a previous post, however, I have still not been able to solve my issue.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

     

    Hi jojo14,

     

    You can add a calculated column to check train status based on training list.

     

    Table formula:

    Train status = [ID] in VALUES('Training finish'[ID]) 

     

     

    Then you can use this status to filter on other tables if they contains the relationships.

     

    Notice: Training finish table is table 3.

     

    BTW, current power bi not support direct filter inverse items based on slicer/filter.

     

    Regards,

    Xiaoxin Sheng

    • jojo14's avatar
      jojo14
      Frequent Visitor

      Hi v-shex-msft,

       

      Thank you for your reply!

       

      Yes i figured that out so far. But what I wish to do is to return the exact training session that a specific ID is missing.

      So instead of true/false, can I return the exact training session that id is missing? It might multiple training sessions.

      • Anonymous's avatar
        Anonymous
        Not applicable

        HI jojo14,

         

        Please try to use below table formula to generate the untrained list:

         

        Untrained = EXCEPT(VALUES(Employee[ID]),VALUES('Training finish'[ID])) 

         

        This table will be change of 'employee' table and 'training finish' table has been changed.

         

        Regards,

        Xiaoxin Sheng