Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

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!

 

Anonymous
Not applicable

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:

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!

 

 

 

Anonymous
Not applicable

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!

Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.