Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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???
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
I can't share any of the data, it's PHI. Are there particular screenshots that would help?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |