Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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.
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!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |