Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a rather unique requirement. I am currently working with medical information and I need to determine how long a patient is admitted to hospital for a respirtory infection. The data is in the following format, where each row is a patient event / episode:
I first need to filter the table and return only those rows where the column 'ConslidatedDiagnosisValues' contains either J18 or J20 or J45. I then need to group the table by UniquePracticeID and UniquePatientID and calculate how long the patient was admitted to hospital. To do this i need to search for '0173' within the 'ConsolidatedChargeCodes'. This would mark the date when the patient was admitted to hospital. For each day the patient remained in hospital a 0111 code is charged. In the example above the patient had 2 stays in hospital. The first started on the 7th July 2017 and ended 13th July 2017. The second started on the 27th August 2017 and ended on the 31st August 2017.
I would like to create a new table with this information grouped as follows:
Thanks for your help
Solved! Go to Solution.
Hi @Anonymous,
For your requirement, you could create the two calculated column and the measure below.
DateOfServiceMin = CALCULATE ( MIN ( 'Table1'[DateOfSubmission] ), ALLEXCEPT ( Table1, Table1[ConsolidatedDiagnosisValues] ) ) DateOfServiceMax = CALCULATE ( MAX ( 'Table1'[DateOfSubmission] ), ALLEXCEPT ( 'Table1', Table1[ConsolidatedDiagnosisValues] ) ) AdmittedDays = DATEDIFF ( MAX ( 'Table1'[DateOfServiceMin] ), MAX ( 'Table1'[DateOfServiceMax] ), DAY ) + 1
Here is the output.
Best Regards,
Cherry
are you looking for DAX or M solution?
also, can you paste sample of your data in text format (e.g. copy from Excel)?
Hi @Stachu,
Looking for a DAX solution
Data as text:
DateOfSubmission | UniquePracticeID | UniquePatientID | ConsolidatedDiagnosisValues | ConsolidatedChargeCodes |
2017/07/07 0:00 | 1433 | 1181011 | J20.9 | 0145|0147|0173|0201 |
2017/07/08 0:00 | 1433 | 1181011 | J20.9 | 0111|0201 |
2017/07/09 0:00 | 1433 | 1181011 | J20.9 | 0111|0201 |
2017/07/11 0:00 | 1433 | 1181011 | J20.9 | 0111|0201 |
2017/07/12 0:00 | 1433 | 1181011 | J20.9 | 0111|0201 |
2017/07/13 0:00 | 1433 | 1181011 | J20.9 | 0111|0201 |
2017/08/27 0:00 | 1433 | 1181011 | J18.0 | 0147|0173|0201 |
2017/08/28 0:00 | 1433 | 1181011 | J18.9 | 0111|0201 |
2017/08/29 0:00 | 1433 | 1181011 | J18.9 | 0111|0201 |
2017/08/30 0:00 | 1433 | 1181011 | J18.9 | 0111|0201 |
2017/08/31 0:00 | 1433 | 1181011 | J18.9 | 0111|0201 |
UniquePracticeID | UniquePatientID | DateOfServiceMin | DateOfServiceMax | AdmittedDays |
1433 | 1181011 | 2017/07/07 | 2017/07/13 | 7 |
1433 | 1181011 | 2017/08/27 | 2017/08/31 | 5 |
Thanks.
Hi @Anonymous,
For your requirement, you could create the two calculated column and the measure below.
DateOfServiceMin = CALCULATE ( MIN ( 'Table1'[DateOfSubmission] ), ALLEXCEPT ( Table1, Table1[ConsolidatedDiagnosisValues] ) ) DateOfServiceMax = CALCULATE ( MAX ( 'Table1'[DateOfSubmission] ), ALLEXCEPT ( 'Table1', Table1[ConsolidatedDiagnosisValues] ) ) AdmittedDays = DATEDIFF ( MAX ( 'Table1'[DateOfServiceMin] ), MAX ( 'Table1'[DateOfServiceMax] ), DAY ) + 1
Here is the output.
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |