Forum Discussion

bigrods's avatar
bigrods
Helper III
5 years ago
Solved

Filter Many to Many relationship by field

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:

CaseloadPt CountReferral LoggedReferral Not Logged
Drug & Alcohol1224973
Throughcare1697

 

This is what I'm getting:

CaseloadPt CountReferral LoggedReferral Not Logged
Drug & Alcohol1265472
Throughcare241014

 

(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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.