cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
novotnajk Regular Visitor
Regular Visitor

Counting Unique Patients between 2 Tables

Hello PBI Community!

Thank you to all of those who have helped thus far with my questions.  I have another hurdle needing assistance:

 

First, I have three (3) tables:

MASTER_PATIENT

MASTER_MEDICAL

MASTER_PHARMACY

 

MASTER_PATIENT has been set as the primary table and the relationships allow me to filter the other two tables via the relationships.  My struggle is finding unique patients in the medical and pharmacy file based on a unique characteristic.  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.  I'm trying to find unique patients who have the same code in both.

 

Here is a sample below:

MASTER_MEDICAL Table

Patient ID          MEDC Code          Plan Spend

123456789        CAR                       $100

ABCDEFGH        RES                        $100

999999999        CAR                       $100

 

MASTER_PHARMACY Table

Patient ID          Major Rx-MG Description         Plan Spend

123456789        CAR                                           $100

ABCDEFGH        CAR                                           $50

999999999         CAR                                          $100

 

I created the following formula, but it isn't giving me the results.  Using the example above, I would expect to get 3 unique CAR (Cardiovascular) patients:

 

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"))
 
Thanks for your help!
5 REPLIES 5
Nathaniel_C Super Contributor
Super Contributor

Re: Counting Unique Patients between 2 Tables

Hi @novotnajk ,

Thanks for the invite!

 

What does Master Patient look like? And the relationships?

Nathaniel

novotnajk Regular Visitor
Regular Visitor

Re: Counting Unique Patients between 2 Tables

 

@Nathaniel_C 

 

Here is the relationship tableRelationships.png

 

Super User
Super User

Re: Counting Unique Patients between 2 Tables

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
novotnajk Regular Visitor
Regular Visitor

Re: Counting Unique Patients between 2 Tables

@Ashish_Mathur  Hi Ashish,

 

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

 

 

Super User
Super User

Re: Counting Unique Patients between 2 Tables

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)