Counting Unique Patients between 2 Tables

Question

Hello PBI Community!Thank you to all of those who have helped thus far with my questions.&nbsp; I have another hurdle needing assistance:&nbsp;First, I have three (3) tables:MASTER_PATIENTMASTER_MEDICALMASTER_PHARMACY&nbsp;MASTER_PATIENT has been set as the primary table and the relationships allow me to filter the other two tables via the relationships.&nbsp; My struggle is finding unique patients in the medical and pharmacy file based on a unique characteristic.&nbsp; This characteristic is a clinical category in a column that is named MEDC Code in the MASTER_MEDICAL file and Major Rx-MG Description in the MASTER_PHARMACY file.&nbsp; I'm trying to find unique patients who have the same code in both.&nbsp;Here is a sample below:MASTER_MEDICAL TablePatient ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MEDC Code&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Plan Spend123456789&nbsp; &nbsp; &nbsp; &nbsp; CAR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100ABCDEFGH&nbsp; &nbsp; &nbsp; &nbsp; RES&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $100999999999&nbsp; &nbsp; &nbsp; &nbsp; CAR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100&nbsp;MASTER_PHARMACY TablePatient ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Major Rx-MG Description&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Plan Spend123456789&nbsp; &nbsp; &nbsp; &nbsp; CAR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100ABCDEFGH&nbsp; &nbsp; &nbsp; &nbsp; CAR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$50999999999&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CAR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $100&nbsp;I created the following formula, but it isn't giving me the results.&nbsp; Using the example above, I would expect to get 3 unique CAR (Cardiovascular) patients:&nbsp;Unique Cardiac Members = CALCULATE(DISTINCTCOUNT(MASTER_PATIENT[Patient ID]),FILTER(MASTER_MEDICAL, MASTER_MEDICAL[MEDC Type] = "CAR"),FILTER(MASTER_PHARMACY, MASTER_PHARMACY[Major Rx-MG Description] = "CAR"))&nbsp;Thanks for your help!

nathaniel_c · Answer

Hi&nbsp;novotnajk&nbsp;,Thanks for the invite!&nbsp;What does Master Patient look like? And the relationships?Nathaniel

novotnajk · Answer

&nbsp;Nathaniel_C&nbsp;&nbsp;Here is the relationship table&nbsp;

ashish_mathur · Answer

Hi,
In the Master Patient Table, is there a clinical category column?

novotnajk · Answer

Ashish_Mathur&nbsp; Hi Ashish,&nbsp;There isn't.&nbsp; The clinical category is tied to a medical or pharmacy claim in their respective files.&nbsp; But, the three tables have the same patients in them, though some may appear in medical and not pharmacy and vice versa.&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
Share the link from where i can download the PBI file.&nbsp; In that file, please ensure that only 3 Tables are present.

Forum Discussion

Counting Unique Patients between 2 Tables

5 Replies

Related content

Counting nulls

Count visits by Patient--then show all patients with a count >=2

Count Of Drivers

Count number of patients each day

Count Measure