Inverse filtering across tables, possible?

[jojo14]

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.

[v-shex-msft]

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]

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.

[v-shex-msft]

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

[jojo14]

Hi Xiaoxin Sheng,

Thanks! - that got me some of the way. However, not everyone is eligible for all of the trainings. I.e., the employees are required to take trainings dependent on their title - project manager, senior project manager and project coordinator.

I need the list to filter in way, so when I in the report filter on a specific content, I have a table that shows the people who have the training (I already have that) and the people who are missing that specific training.

From the solution you have provided, it will not show any of the names from the list that have completed trainings. although it might be that a peson from that list is missing another training than the one shown (dependent on his role)

Hope it makes sense? 🙂

Kind regards,

Jonathan

[jojo14]

I can perhaps rephrase the question a bit:

Can I have a dynamic table that shows a column of the employee IDs and a column of the corresponding trainings that they have NOT completed? I.e. an opposite table of the one where they have completed the trainings.

Kind regards,

Jonathan

[v-shex-msft]

Hi @jojo14,

In my opinion, you only need to filter out all the employee id who suitable the requirement.
Then use except function to get the remained employee id based on employee table and above list.(there are untrained list)

Regards,

Xiaoxin Sheng

[jojo14]

Hi v-shex-msft,

It seems that this solutions gives me a permanently filtered list. I.e. a list where everone who has a training (any kind of training) will be filtered away.

What i would like is, when i choose a specific trainingin one visual on the page, i would like it to filter the table so that it shows who has not had completed that specific training.

[v-shex-msft]

Hi @jojo14,

>>What i would like is, when i choose a specific trainingin one visual on the page, i would like it to filter the table so that it shows who has not had completed that specific training.

For your requirement, I think calculate table is impossible to achieve your requirement.  I'd like to write a measure to check training status, can you please share some sample data?

Notice: please hide/remove your privacy data before sharing.

Regards,

Xiaoxin Sheng

[jojo14]

Hi Xiaoxin Sheng,

Sorry for the late response.

I think i have narrowed what I am missing down to this. I believe i need to use query editor some how. I have the two first tables and wish to get the third one based on the previous two.

[MarcelBeug]

That would be a straightforward table merge.

let
    Source = Table.NestedJoin(Employee,{"Role"},Training,{"Role"},"Training",JoinKind.LeftOuter),
    #"Expanded Training" = Table.ExpandTableColumn(Source, "Training", {"Training"}, {"Training"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Training",{{"Training", Order.Ascending}})
in
    #"Sorted Rows"

This is how it is created in Excel, similar steps in Power BI, Query Editor (I adjusted column name "Training.1" to "Training" after the recording):

This is where you can find "Merge Queries as New" in the Query Editor of Power BI Desktop:

[jojo14]

Thank you! Thought I tried that already, but could not get it to work.

Thank you for the very elaborate guidance. One further question:

Is it possible for me to merge the employee and training table without the use of the roles column? Essentially what I want is a table that depicts every single training employee.

[DAX0110]

Hi @jojo14,

 I started out testing a few ideas, but one thing led to another and there's a working prototype

It looks like this in my Power BI Desktop: Screens

The PBIX file, if you want to see how I did it: Prototype

You can contact me with any questions.

Have fun!