cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
duydonquanbach
Frequent Visitor

How to calculate number of CPOE and turnaround time for each patient

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. 

1 ACCEPTED 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. 

View solution in original post

8 REPLIES 8
v-kelly-msft
Community Support
Community Support

Hi  @duydonquanbach ,

 

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:

v-kelly-msft_0-1623663257619.png

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 @duydonquanbach ,

 

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:

v-kelly-msft_0-1623720256384.png

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

 

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. 

View solution in original post

Hi  @duydonquanbach ,

 

Is your issue solved now?

 

Best Regards,
Kelly

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

Hi  @duydonquanbach ,

 

Brilliant! Waiting for your good news!😀

 

Best Regards,
Kelly

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.