Merge 2 sets of data elements then subtract

[jonnyA]

Hello,

I need to marry a DOS to specific Procedure Codes.  I need one measure that marries a DOS to the following Procedure Codes, 99221, 99222, 99223 (These Procedure codes represent Admit Date)

Then I need another measure that marries a DOS to the following Procedure Codes, 99238 and 99239 (These Procedure Codes represent the Discharge Dates)

Then I will need a measure that subtracts those 2 measures.

My goal is to find the Length of Stay of a patients stay.

[v-yangliu-msft · Accepted solution]

Hi  @jonnyA ,

According to the prompt error:

It is found that it is a data type problem, you can check whether [Procedure_Code] is of Text type.

If it is of Text type, you can modify Admit Date and Discharge Dates to the following:

Admit Date =
CALCULATE(MAX('Table'[DOS]),FILTER(ALL('Table'),'Table'[Procedure_Code] in {"99221","99222","99223"}&&'Table'[Patient_First_Name]=MAX('Table'[Patient_First_Name])))

Discharge Dates =
CALCULATE(MAX('Table'[DOS]),FILTER(ALL('Table'),'Table'[Procedure_Code] in {"99238","99239"}&&'Table'[Patient_First_Name]=MAX('Table'[Patient_First_Name])))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonnyA]

@v-yangliu-msft , thank you so much, that worked!!!

[v-yangliu-msft · Accepted solution]

Hi  @jonnyA ,

This is the same as above, it is a text type, you can change the function.

{99221,99222,99223}To {"99221","99222","99223"}

{99238,99239} changed to {"99238","99239"}

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonnyA]

@v-yangliu-msft 

I'm having the same issue with another formula.  Have any advice on how to fix so it will work with my data??

[v-yangliu-msft · Accepted solution]

Hi  @jonnyA ,

According to the prompt error:

It is found that it is a data type problem, you can check whether [Procedure_Code] is of Text type.

If it is of Text type, you can modify Admit Date and Discharge Dates to the following:

Admit Date =
CALCULATE(MAX('Table'[DOS]),FILTER(ALL('Table'),'Table'[Procedure_Code] in {"99221","99222","99223"}&&'Table'[Patient_First_Name]=MAX('Table'[Patient_First_Name])))

Discharge Dates =
CALCULATE(MAX('Table'[DOS]),FILTER(ALL('Table'),'Table'[Procedure_Code] in {"99238","99239"}&&'Table'[Patient_First_Name]=MAX('Table'[Patient_First_Name])))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonnyA]

@v-yangliu-msft , do you know why this fromula is not working?

AR From Billed Claims = SUM('New_Demo_Charge_Dataset'[Balance]) - [AR Related To Credentialing]

You have helped before wirth an issue similar so that is why i asm asking.  

Thank you in advance for your help!

See below for error ...

[jonnyA]

@v-yangliu-msft Thank you soooo much!!!!!!!!!!

[jonnyA]

@v-yangliu-msft , thank you so much for the example.  But my table goes blank when i add the measures I just created, but I dont know why?  

One differance I noticed from my dataset to yours is that your "Procedure Code" has the "E" in front of it, where my Procedure Code does not.  Not sure if that is the issue or not.

[v-yangliu-msft]

Hi  @jonnyA ,

I created some data:

Here are the steps you can follow：

Find the Length of Stay of a patients stay

1. Create measure.

Admit Date =
CALCULATE(MAX('Table'[DOS]),FILTER(ALL('Table'),'Table'[Procedure_Code] in {99221,99222,99223}&&'Table'[Patient_First_Name]=MAX('Table'[Patient_First_Name])))

Discharge Dates =
CALCULATE(MAX('Table'[DOS]),FILTER(ALL('Table'),'Table'[Procedure_Code] in {99238,99239}&&'Table'[Patient_First_Name]=MAX('Table'[Patient_First_Name])))

Length of Stay of a patients stay=
DATEDIFF([Admit Date],[Discharge Dates],DAY)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jonnyA]

@v-yangliu-msft I want to show you the errors that I am getting ... 

Admit Date Measure 

Discharge Date Measure

Length of Stay of a Patients Stay 

[amitchandak]

@jonnyA , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jonnyA]

@amitchandak 

I'm not sure if this will help or not, but here is a small sample size of data.  

Do you know how to create a measure that will only pull Procedure Codes 99221, 99222 and 99223?