Forum Discussion
Responsive ResidentMerge 2 sets of data elements then subtract
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.
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.
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.
11 Replies
- amitchandak
Super User
jonnyA , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
jonnyAI'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?
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
Anonymous I want to show you the errors that I am getting ...
Admit Date Measure
Discharge Date Measure
Length of Stay of a Patients Stay
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
Anonymous Thank you soooo much!!!!!!!!!!
- jonnyA
Anonymous , 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
Anonymous , 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.
- jonnyA
Anonymous
I'm having the same issue with another formula. Have any advice on how to fix so it will work with my data??
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
Anonymous , thank you so much, that worked!!!
