How can I filter on a shared field across many tables WITHOUT active relationships?

[sasdfasdfsad]

Let's imagine a patient billing medical records system.

Assume that we have a way to track individual people properly with a mix (like a unique HASH or something) of SSN, resident ID, DOB, name, whatever... so they get a "IndividualGuid".

We have lots of tables in our system, patient records, visits, billing, medical procedures, etc... doesn't matter.  They all tie to each other in various ways via a proper design.

Here is the question:

Let's say I want to search the ENTIRE system via this magic IndividualGuid.  I want to see all the instances of records for any of these people.

I can't just drop in IndividualGuid table and drag the IndividualGuid column to this table for every single table and make it active because all these existing tables already have active relationships and it would create ambiguous relationships.

What approach would someone use to make a single slicer that lets you filter down on this?

I want a PBI page that lets me search by IndividualGuid and return matches in each table.

Maybe a page with 10 data grids one per table of interest.

How can a person accomplish this?

I'm completely lost on the best design approach, way to populate the slicer, and how to make it work.

Suggestions?

[v-ssriganesh]

Hello @sasdfasdfsad,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @sasdfasdfsad,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @sasdfasdfsad,
Thank you for posting your query in the Microsoft Fabric Community Forum.

I’ve reproduced your scenario in Power BI Desktop using a disconnected IndividualGuid slicer and measure-based filtering, matching the data grains and role transitions you described (student, teacher, bill payer, etc.).

With this approach, selecting a single IndividualGuid correctly returns matching records across all unrelated tables, while tables with no matching rows remain empty which aligns with your requirement to search the entire system without introducing ambiguous relationships.

For your reference, I’ve attached a sample .pbix file demonstrating the complete setup and expected output.

Best regards,
Ganesh Singamshetty.

[TomMartens]

Hey @sasdfasdfsad ,

I do not understand why using a people dimension creates ambiguous path in a dimensional model, but anyhow, may you can the measure approach of @GeraldGEmerick , the Smart Filter of OKVIZ (https://okviz.com/), and a dimensional modeling approach.

Regards,
Tom

[sasdfasdfsad]

Because the relationships won't all be based off a person ID.

Take a class system, Teachers, Courses, students assigned to classes.

Here is an ERD just from a quick search online

The teacher table and the student table could have the IndividualGuid column.  Then we don't even show a grades table, and that would be grades to students.  Bill payments due, etc...  The person responsible for the bill may not be the student so we have another person table there (somewhere) showing who is paying the bill.  That table is going to have an IndividualGuid in it.

In reality end of the day will be 1 master Individuals table.

Now a person wants to search ALL The tables that may have a record for a single individual.

There's no natural active relationship that can run from the Individuals table to the Student, Teacher, Billing, etc.... they have to use their own separate PK and FK IDs for all that so they can have a proper 1:m or 1:1 or sadly M:M setup depending.


BUT!

People will want to query the entire system to find all instances of a single individual.  Especially if they had a full lifecycle, started as a student, became a teacher, had their own kids who they paid bills for under their own student IDs, etc, etc...

Then we get related things like AD logins, hardware assigned, etc, etc.

Even with a single perfect unified Individuals table, we can't use the IndividualGuid as a linking key between all those other tables because the grain is wrong.

There's no relationship between teacher and student, it would be teacher to course instance to student.

Later on a student could easily come back as a teacher so those tables would have the IndividualGuid column but there's no real relationship between them.

[TomMartens]

Hey @sasdfasdfsad ,

Thank you for the explanation. Can you provide a sample model with sample data that has the role-playing dimensions like student and teacher?

Regards,
Tom

[GeraldGEmerick]

@sasdfasdfsad You could create a measure that returns True or False or 1 or 0 depending on whether the ID is in the row of the table. For example, you could create a measure like the following:

Measure = IF( MAX( 'Table'[ID] ) = SELECTEDVALUE( 'Slicer'[Value] ), 1, 0 )

You can then filter your table visual for Measure = 1.