Filter then group a table

[Anonymous]

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

[Stachu]

are you looking for DAX or M solution?

also, can you paste sample of your data in text format (e.g. copy from Excel)?

[Anonymous]

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.

[v-piga-msft · Accepted 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