cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Discharge department

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... 🙂

1 ACCEPTED SOLUTION
Super User

@bdeleur
For now this is what I can provide you. The difference between Count 2 and Count 3 is that count 3 shall not count discharged from same department unless there is a discharge date. The difference shall not be noticed using the sample data as there are no blanks in the end date.

24 REPLIES 24
Super User

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
)
``````
Helper III

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?

Super User

@bdeleur

``````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
)
)``````
Helper III

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

Super User

@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
)
)``````
Helper III

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

Super User

@bdeleur
For now this is what I can provide you. The difference between Count 2 and Count 3 is that count 3 shall not count discharged from same department unless there is a discharge date. The difference shall not be noticed using the sample data as there are no blanks in the end date.

Helper III

Hi @tamerj1 ,

Sorry for the delay. It was a bussy week.

The count seams to work.

I have a question still: the totals give a differnt count the the sum in the matrix.

And I want to skip the count the movement between the same department, it isn't relevant.

Can you help me to understand it?

Super User

HI @bdeleur
I can understand when the total counts less. That is expected. If the same reference extends over the two months then it will be counted in both months while at the total level it will be counted once. Therefore, this is a non-additive calculation by nature like distinctcount. However, it is up to your business logic to determine whether to force additivty or not.
Don't be confused about the scenarios where the total counts more. Notice that this only happens when Feb returns blank value. Which means that some references were neither counted in Feb. nor in Mar. Apparently one record in Feb. and one record on Mar. so it will be counted in neither but the in the total level.

To exclude same department movements just return to the very first solution.

Helper III

Tnx for the answer. When I use my calander the count is correct, so I will use that.

Tnx for all your help, coudn't done it whitout you and I learned a lot.

I will accept your help as the solution.

Super User

@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.

Helper III

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?

Super User

@bdeleur
If based on the start date and you have a dimDate table then I guess it should work.

Super User

@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.

Helper III

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

Helper III

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

Helper III

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.

Super User

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?

Proud to be a Super User!

Helper III

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?

Super User

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 ?

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors