Data missing on bi-directional filter in relationships

[olimilo]

I have the following data model. All tables except the Scheme Qualifications <-> Scheme List are connected via the ID key. The goal here is to filter the Profile table (list of employees) by Technical Scope, Language and Scheme List, and vice versa (ie: to see what their qualifications are). I can do this using bi-directional filter on the category tables but some of the employees get listed out/don't appear because they don't have matching records in the category tables.

I tried doing the M:M route with the Scheme list (which has a name field drawn from the Profile table) and making it one-directional but it doesn't quite work the way it should. When I filter using the Scheme table, the data doesn't get filtered up to the Profile table. Same thing happens the other way around.

In this case, how should my data be modeled to achieve the intended result?

[v-danhe-msft]

Hi @olimilo,

Could you have tried to modify the highlight relationship in below picture from single to both:

Regards,

Daniel He

[olimilo]

Hi Daniel @v-danhe-msft

Thanks for the suggestion. That was something that I've already tried before but the problem is, doing so will filter out (ie: exclude) the employees that do not have a matching scheme in the Scheme Qualification table.

So, if I had the following table:

 

Your record from the Profile table will be omitted because there's no matching record under the Scheme Qualification table.