Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I have data like that
HN visit time CPOE order time CPOE name
A 10:00:00 11:00:00 Drug A
A 10:00:00 11:02:00 Drug B
B 9:00:00 9:10:00 Drug C
B 9:00:00 9:20:00 CT scan
C 9:00:00 9:10:00 Drug D
C 9:00:00 9:20:00 MRI
C 9:00:00 9:10:00 Blood count
C 9:00:00 9:20:00 CT scan
I would like to classify patients into three categories:
1. Patients with consultation only (only prescripted with drug, like patient A)
2. Patients with consultation and 1 CPOE order (may be lab tests like blood count or radiography like CTscan) (like patient B)
3. Patient with consultation and >=2 CPOE orders (like patient C)
And I'd like to calculate turnaround time for each patient (time interval from visit to CPOE order).
Would anybody help me with that? Thank you very much.
Solved! Go to Solution.
Yeah, that's really a good idea. I'll try to make a reference table in which have CPOE name and their code so I can link it to the fact table. Let's me try it. Thank you so much for your support, Kelly.
Hi @Anonymous ,
Create 2 columns as below:
CPOE new =
var _search=SEARCH("Drug",'Table'[CPOE name],1,0)
Return
IF(_search<>0,"Drug",'Table'[CPOE name])
category =
var _count1=CALCULATE(COUNT('Table'[CPOE new]),FILTER(ALL('Table'),'Table'[HN ]=EARLIER('Table'[HN ])&&'Table'[CPOE new]="Drug"))
var _count2=CALCULATE(DISTINCTCOUNT('Table'[CPOE new]),FILTER(ALL('Table'),'Table'[HN ]=EARLIER('Table'[HN ])&&'Table'[CPOE new]<>"Drug"))
Return
IF(_count1<>0&&_count2=0,
"category1",
IF(_count1<>0&&_count2=1,
"category2",
IF(_count1<>0&&_count2>=2,"category3",BLANK())))
And you would get the category classified as below:
If you wanna calculate the turnaround time for each CPOE order of each patient,using below dax expression:
turnaround time = DATEDIFF('Table'[visit time ],'Table'[CPOE order time ],MINUTE)
If you wanna calculate the total turnaround time for each patient,using below dax expression:
turnaround time 2 =
var _mintime=CALCULATE(MIN('Table'[visit time ]),FILTER(ALL('Table'),'Table'[HN ]=EARLIER('Table'[HN ])))
var _maxtime=CALCULATE(MAX('Table'[CPOE order time ]),FILTER(ALL('Table'),'Table'[HN ]=EARLIER('Table'[HN ])))
Return
DATEDIFF(_mintime,_maxtime,MINUTE)
Pls check my .pbix file attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Thank you so much v-kelly-msft
In fact, the names of drugs are not simple as Drug A or Drug B. They are just like Paracetamol, Erythomycin etc...So when I created the "CPOE new" as you suggested, it didn't work.
Hi @Anonymous ,
Do you have a list of the drugs?If so,you could modify search function to XXX in {},such as below:
CPOE new =
IF('Table'[CPOE name] in {"A","B","C","D"},"Drug",'Table'[CPOE name])
And you would also get the same result:
For the updated .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Yeah, I have the list but the list contains more than 200 drugs, too much if we put it in the function, right. In addition, every time doctors make drug orders, they enter different drug's name, so it is so hard to make a list of drug 😭😭.
Hi @Anonymous,
Then could you find a way to distinguish consultation from others?
Suppose you have a table or a list containing the drugs,you could modify the expression as "
CPOE new =
IF('Table'[CPOE name] in filters 'table name'[Column name],"Drug",'Table'[CPOE name])
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Yeah, that's really a good idea. I'll try to make a reference table in which have CPOE name and their code so I can link it to the fact table. Let's me try it. Thank you so much for your support, Kelly.
Hi @Anonymous ,
Is your issue solved now?
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
Brilliant! Waiting for your good news!😀
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
105 | |
105 | |
88 | |
61 |
User | Count |
---|---|
165 | |
133 | |
132 | |
95 | |
86 |