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

CrossFiltering Records in Same Table - Medical Claims and Corresponding Pharmacy Claims

Hi!

 

I have created a master patient file that contains all medical diagnoses and spend in two columns by date and columns that have the same for pharmacy.  I'm trying to create two tables: 

1) A table that allows me to click on a medical diagnosis to show patients and medical spend, and

2) A table that cross filters patients with that medical diagnosis to show the drug name and pharmacy spend

 

Note:  These records are in the same table, but I can separate them into medical and pharmacy if that helps.  Here are the columns:

 

patient_ID

reporting_date

Diagnosis_Description

Medical_Paid

Drug_Name

Pharmacy_Paid

 

Ideally, the two tables would show the following:

 

Table 1

Diagnosis Description    # of Patients    Medical Paid

Asthma                           2                      $500

 

Table Two

Drug Name                    # of Patients    Pharmacy Paid

Symbiocort                     1                      $500

ProAir HFA                      1                      $100

 

Thanks!

 

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@novotnajk ,

 

Generally, you may click Modeling-> New Table and create two temp tables using dax like pattern below:

Table1 =
SUMMARIZE (
    Table,
    Table[Diagnosis Description],
    Table[# of Patients],
    Table[Medical Paid]
)
Table2 =
SUMMARIZE (
    Table,
    Table[Drug Name],
    Table[# of Patients],
    Table[Pharmacy Paid]
)

Community Support Team _ Jimmy Tao

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

@v-yuta-msft 

 

Hi Jimmy,

 

Not quite what I was thinking.  So, I decided to break up the MASTER_PATIENT table in PowerBI Query into two (2) tables:

1)  MASTER_MEDICAL

2)  MASTER_PHARMACY

 

The only common field between the two is Patient ID.

What I need is to be able to filter patients in the medical file  to see what pharmacy use they have when selecting a diagnosis from the medical table.  To re-visualize this imagine the following:

 

MASTER_MEDICAL 

Diagnosis Description           # of Patients           Med Spend

Asthma                                  4                              $500

Diabetes                                3                              $1,000

 

if by clicking Diabetes in the MASTER_MEDICAL table, the MASTER_PHARMACY table below would show the following:

Medication                            # of Patients            Rx Spend

Humalog                                2                              $200

Glucophage                           1                               $150

 

Note that I will want to crossfilter in the same fashion by clicking on Humalog to see Diabetes above.  Any idea on how I should do this?  Should I create a master patient file that only contains Patient ID and create a relationship between all three?  I'm stuck here.  Thanks for your help!                                   

novotnajk
Resolver I
Resolver I

One clarification, I'm trying to create two visual tables in Power BI


@novotnajk wrote:

Hi!

 

I have created a master patient file that contains all medical diagnoses and spend in two columns by date and columns that have the same for pharmacy.  I'm trying to create two tables: 

1) A table that allows me to click on a medical diagnosis to show patients and medical spend, and

2) A table that cross filters patients with that medical diagnosis to show the drug name and pharmacy spend

 

Note:  These records are in the same table, but I can separate them into medical and pharmacy if that helps.  Here are the columns:

 

patient_ID

reporting_date

Diagnosis_Description

Medical_Paid

Drug_Name

Pharmacy_Paid

 

Ideally, the two tables would show the following:

 

Table 1

Diagnosis Description    # of Patients    Medical Paid

Asthma                           2                      $500

 

Table Two

Drug Name                    # of Patients    Pharmacy Paid

Symbiocort                     1                      $500

ProAir HFA                      1                      $100

 

Thanks!

 



Deskop.

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.