Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataDiva
Helper II
Helper II

Relationship/calculation not behaving at all as expected

I have a sensitive database, so I can't share it, but I'm hoping by showing images I can get some help! I am trying to calculate two measures--total records (called "total visits") and unique patients. I have a record ID, which only occurs once, and a patient ID (called "MPI"), which is repeated any time it's the same patient. I've created two measures:

 

Total Visits=COUNT('cha vwCHAprimary_Limited'[RecordID])

Unique patients=DISTINCTCOUNT('cha vwCHAprimaryExt'[MPI])

 

This seems simple enough. However, it's not working when I try to use a slicer from another table, which is connected by a relationship to the same table that contains the recordID. It definitely filters records, but I sometimes get more unique patients than total records, which should never happen. 

 

Here's how all my tables are related:

relationships of my tablesrelationships of my tables

 

You can see that MPI resides in the "EXT" table, and my syndromes, which is what I want to filter by, resides in the PopHealthDX table, which are both related to the primary table where the recordID count comes from. If I clear the slicers, I get 8.1 million total visits and 2,4 million unique patients, which is correct. But if I select, say, "External Cause of Injury" from the PopHealthDX Syndrome field in the slicer, I get 10,555 total visits and 45,938 unique patients--totally wrong (and impossible). If I select instead a different field ("substance abuse") I get a reasonable output of 605k visits and 330k unique patients. I don't understand AT ALL what is going on.

 

Can anyone help???

 

 

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi,@DataDiva

I have tested on my side, but not reproduce the issue. and we need more detail steps for us.

You can do a demo file ,Just leaving only little data that can reproduce your issue. and give us some screenshot. You can upload it to OneDrive or Dropbox and post the link here. 

 

Best Regards,

Lin

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

I can't share any of the data, it's PHI. Are there particular screenshots that would help?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.