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

[Anonymous]

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. 

[v-kelly-msft]

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!

[Anonymous]

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.

[v-kelly-msft]

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!

[Anonymous]

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 😭😭. 

[v-kelly-msft]

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!

[Anonymous · 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. 

[v-kelly-msft]

Hi  @Anonymous ,

Is your issue solved now?

Best Regards,
Kelly

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

[v-kelly-msft]

Hi  @Anonymous ,

Brilliant! Waiting for your good news!😀

Best Regards,
Kelly

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