topic Re: Discharge department in DAX Commands and Tips

Discharge department

bdeleur — Mon, 27 Feb 2023 14:36:44 GMT

Hi,

I want to make a report witch contains the following information.

Out of a big data file I want to extract the follow up department.

So I have department A and how many have a transfer (discharge) to deparment B & C etc.

Reference	Start date	End date	Discharge (out of hospital)	Department
1	1-11-2022 16:00	10-11-2022 10:11		A
1	10-11-2022 10:11			B
2	2-11-2022 15:35	3-11-2022 13:34	3-11-2022 13:34	A
3	1-11-2022 09:15	2-11-2022 10:24		A
3	2-11-2022 10:24	3-11-2022 18:12	3-11-2022 18:12	C

So you can see that reference 1 transfer from department A to B at 10-11-2022 10:11.

Reference 2 is discharged out of the hospital direct from department A.

Reference 3 transfers to department C and gets discharged out of that department (I don't want to know that discharge, only the report of department A to witch department the patient is transferd or discharged).

The file contains every department in the hospital.

Hope some can make the measure because with my knowledge... 🙂

Re: Discharge department

andhiii079845 — Mon, 27 Feb 2023 19:30:02 GMT

Hi,
can you please give more information what the measure should count exactly:
Tranfer of patient from A,B,C, to A,B,C? Like a matrix?

From / to	A	B	C
A	1	4
B	2	....
C	3	....

Re: Discharge department

bdeleur — Tue, 28 Feb 2023 08:46:38 GMT

Hi,

I really like the idea of the matrix. Didn't think about that. So I would like tot see that.

From there I think it is easy to filter for every department.

Is that possible?

Re: Discharge department

andhiii079845 — Tue, 28 Feb 2023 08:52:34 GMT

Yes, you can have one filter for the starting and one filter for the ending department.

But i need more example data to create the measure and you have to make more clear how you handle or want to see the discharge out of the hospital. Also what is with cases like A -> B -> A -> out of the hospital how you count it? without the station between start and end?

Re: Discharge department

bdeleur — Tue, 28 Feb 2023 09:36:52 GMT

Hi, andhiii079845

A movement between departments uses the start and end date. When the end date and dismissal date are the same then the patient is discharged form that department.

patient 1 only had a transfer a to B, no discharge out of hospital

patient 2 didn't moved to another department and gets discharged out of hospital

patient 3 moved from department A to B and gets discharged out of B

Is that what you need?

Re: Discharge department

andhiii079845 — Tue, 28 Feb 2023 09:45:21 GMT

Thank you for the explanation. So the discharge out of the hospital is not relevant, right? How you want to handle the case if a person go from A to B to C or A to B to A. ? Should every transfer should be count ?

Re: Discharge department

tamerj1 — Tue, 28 Feb 2023 11:43:27 GMT

Hi bdeleur
Please refer to attached sample file with the proposed solution

Count = SUMX ( VALUES ( 'Table'[Reference] ), VAR CurrentTable = CALCULATETABLE ( 'Table' ) VAR FirstRecord = TOPN ( 1, CurrentTable, 'Table'[Start date], ASC ) VAR LastRecord = TOPN ( 1, CurrentTable, 'Table'[Start date] ) VAR FirstDepartment = MAXX ( FirstRecord, 'Table'[Department] ) VAR LastDepartment = MAXX ( LastRecord, 'Table'[Department] ) RETURN IF ( FirstDepartment <> LastDepartment && MAX ( 'To'[Department] ) = LastDepartment && MAX ( 'FROM'[Department] ) = FirstDepartment, 1 )

Re: Discharge department

bdeleur — Tue, 28 Feb 2023 16:21:56 GMT

The discharge out of hospital is only relevant when there is no next department. Then "discharge" needs to count as an movement, but only for the last deparment for that patient.

Every transfer needs to be counted, even they move back to a department

Re: Discharge department

bdeleur — Tue, 28 Feb 2023 16:23:07 GMT

Hi tamerj1 ,

Tnx. I will aply it to my data and see what the code makes of it. I will come back with the result.

Re: Discharge department

andhiii079845 — Tue, 28 Feb 2023 16:24:56 GMT

Perhaps try tamerj1 solution if it works what you looking for. 🙂
If not, let us know.

Re: Discharge department

bdeleur — Wed, 01 Mar 2023 09:04:06 GMT

Hi,

I applied the code and it seems to work fine. I have to do and check to the outcome to be sure. Just 1 thing is missing: the code doesn't count the patients who have a discharge out of hospital.

How can we do that?

Re: Discharge department

tamerj1 — Wed, 01 Mar 2023 09:20:55 GMT

bdeleur
Please try

Count = SUMX ( VALUES ( 'Table'[Reference] ), VAR CurrentTable = CALCULATETABLE ( 'Table' ) VAR FirstRecord = TOPN ( 1, CurrentTable, 'Table'[Start date], ASC ) VAR LastRecord = TOPN ( 1, CurrentTable, 'Table'[Start date] ) VAR FirstDepartment = MAXX ( FirstRecord, 'Table'[Department] ) VAR LastDepartment = MAXX ( LastRecord, 'Table'[Department] ) VAR DischargeDate = MAXX ( LastRecord, 'Table'[End Date] ) RETURN IF ( AND ( FirstDepartment = LastDepartment, DischargeDate <> BLANK () ) && MAX ( 'To'[Department] ) = LastDepartment && MAX ( 'FROM'[Department] ) = FirstDepartment, 1 ) )

Re: Discharge department

bdeleur — Wed, 01 Mar 2023 09:59:55 GMT

Now the matrix stays empty when I apply the code in your file.

Re: Discharge department

tamerj1 — Wed, 01 Mar 2023 11:04:00 GMT

bdeleur
I guess you are right. Sorry did not test. Pleas try

Count = SUMX ( VALUES ( 'Table'[Reference] ), VAR CurrentTable = CALCULATETABLE ( 'Table' ) VAR FirstRecord = TOPN ( 1, CurrentTable, 'Table'[Start date], ASC ) VAR LastRecord = TOPN ( 1, CurrentTable, 'Table'[Start date] ) VAR FirstDepartment = MAXX ( FirstRecord, 'Table'[Department] ) VAR LastDepartment = MAXX ( LastRecord, 'Table'[Department] ) VAR DischargeDate = MAXX ( LastRecord, 'Table'[End Date] ) RETURN IF ( OR ( FirstDepartment = LastDepartment && DischargeDate <> BLANK (), MAX ( 'To'[Department] ) = LastDepartment && MAX ( 'FROM'[Department] ) = FirstDepartment ), 1 ) )

Re: Discharge department

bdeleur — Thu, 02 Mar 2023 20:54:10 GMT

I get some information now. But I'm trying to understand what I see.

I see the movements for every department but I don't see the next department.

I think I didn't meanson that well enough, sorry for that. And I like to see it like this:

This is when I selected/slice or filter department A

Re: Discharge department

bdeleur — Thu, 02 Mar 2023 20:53:34 GMT

tamerj1I know you're very busy but please don't forget me 🙂

Re: Discharge department

tamerj1 — Fri, 03 Mar 2023 05:04:08 GMT

bdeleur
Sorry I didn't notice your reply. In order to provide your with accurate solution I need a sample dummy data (large just enough to provide significant output) along with the expected results. You can do that in excel or whatever tool you prefer. A sample PBIX file would also be great.

Re: Discharge department

bdeleur — Mon, 06 Mar 2023 08:27:48 GMT

Hi, yes I understand that. I'll export my data and anomymize it.

Re: Discharge department

tamerj1 — Tue, 07 Mar 2023 13:06:35 GMT

bdeleur
Initially the matrix analysis was based on "From" "To". Now I see a different matrix with "Months" in the columns of the matrix. For me this is a completely different analysis. Please clarify precisely what are you trying to accomplish.

Re: Discharge department

bdeleur — Tue, 07 Mar 2023 13:15:45 GMT

Sorry for the confusion. With my short of knowledge I thought it was possible if you identifie the movements (matrix "from" to "to") you can count how many movements (unique reference) from a department to another, where you can filter the start department. So how many references are moved from A to B, to C etc and when you change the filter from B to A, to C etc. Displayed per date (month will do).

I gave you the data, is it helpfull?