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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!