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
novotnajk
Resolver I
Resolver I

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
Ashish_Mathur
Super User
Super User

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/

@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.

 

 

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/
Nathaniel_C
Super User
Super User

Hi @novotnajk ,

Thanks for the invite!

 

What does Master Patient look like? And the relationships?

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




 

@Nathaniel_C 

 

Here is the relationship tableRelationships.png

 

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.