Filter Many to Many relationship by field

[bigrods]

Hi,

I have an issue with trying to get my data together that I hope someone could help with and I can explain adequately!

I have 2 tables: Referrals and Caseload.

The Referrals table is a list of patients with a referral to 2 services across different sites - it has a "Referral Date" field that is any can be any date.  A patient could have a referral opened at multiple sites in the month. I would like to create a Page Filter for "Site" in the referrals table

The Caseload table is a list of patients on each caseload at the beginning of each month (as it's a snapshot) - it has a "Month" field that is set as the 1st of each month. Again, a patient could be on a caseload at multiple sites at the month.

For each month I need to see both the cumulative and opened referrals, so created a date table to help me do this. The date table is based on the referral table. I have created a measure in the referral table that gives me these no's accurately.

I have a filter on the page based on "Site" in the Referrals table and I then need to see the no's on the caseload at each month across each site and where referrals are cumulative or opened and if each caseload entry has a referral logged or not but the issue I'm having is that: 

I can't seem to limit the caseload no's by Site (in the Referrals table) - the relationship between Referral > Caseload is by Patient ID, so when I filter the page by Site, it gives me all the referrals for that site, but then the caseload no's include where Patient ID's appear at other sites too. The relationship is Many - Many as a Patient could register and re-register and have multiple referrals at multple sites and also be allocated onto a caseload multiple times.

I just need to limit the caseload no's by Site as well if possible? I have created a key called PatientSite on both tables joining the Patient ID and Site values but the no's are still the same.

This is what no's should be:

Caseload	Pt Count	Referral Logged	Referral Not Logged
Drug & Alcohol	122	49	73
Throughcare	16	9	7

This is what I'm getting:

Caseload	Pt Count	Referral Logged	Referral Not Logged
Drug & Alcohol	126	54	72
Throughcare	24	10	14

(the extra no's are where Pts are on caseloads at other sites - I just need to limit those where at the Referral > Site. The Many - Many relationship is stopping this I think.

The "Referral Logged" measure is as follows: 

Referral Logged = CALCULATE(COUNT('All Caseloads'[Patient id]),FILTER('All Caseloads','All Caseloads'[referral_ID_exists]="Yes"))

The "Referral Not Logged" measure is as follows:

Referral Not Logged = CALCULATE(COUNT('All Caseloads'[Patient ID]),FILTER('All Caseloads','All Caseloads'[referral_ID_exists]="No"))

I hope I've explained ok! It feels like I've rambled!

[Anonymous · Accepted solution]

Hi @bigrods 

Many-to-many relationships can cause a lot of problems, which is why it is also called a weak relationship. In most cases, it can be solved by creating a shared dimension and creating a one-to-many relationship from the shared dimension to the fact table. Avoid this type of relationship in your model. The current scenario is more suitable for sharing dimensions.

I found an article about one to many and many to many relationship between tables ,you can make some changes through this article .

https://radacad.com/many-to-one-or-many-to-many-the-cardinality-of-power-bi-relationship-demystified

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

I suggest you create dimension tables for the fields common to both tables and create one-to-many relationships. You can then use these dimension tables in your filters, slicers measures etc...

It is highly recommended to avoid many-to-many relationships since they can produce unexpected (and hard to detect) results.