census

[Funkmiester]

I have posted this in the past and have made limited progress, I have also been away from this for about 6 months and am now back to needing it! It is specifically to count the number of patients in an Emergency Department every hour. The data is supplied

line by line with one attendance per line-[EDTable]. On that line is all the details of the attendance and patient with an admission date/time and a discharge date/time. In the past in excel I have used 2 pivot tables with an admission count per hour and a discharge count per hour then in the third table calculated a running total (RT=(RT+(Admissions-Discharges)) every hour.

For some of the basic work, it's easy to create these tables as static in exel and then just upload the heatmap table separately but I need this to work dynamically from within powerBI as I would like to slice the data using different patient characteristics

I can create individual measures for admissions per hour of the day, discharges per day but not join them and continuing the count from day to day is also tricky. I have about 100,000 rows and 50 columns.

I've tried many different ways in both DAX and PowerQuery M but always come unstuck either counting past midnight or creating a separate table an having to create a relationship between the arrivalDT and dischargeDT?

I have tried YTD type measure to count all who have arrived up to that point but keep running into syntax problems due to my limited knowledge.

I literally have run out of ideas, I'm hoping the gunius hive mind can help

This shows a typical row layout in the [EDTable]

This shows how I used to do it in excel with the required output date/hour table, however I need to do this within PBI

The required output would be an active measure of patients in the department in that hour of the day that I could use in heatmap table or on graphs and be dynapically slicable based on fields in the master [EDTable]

This is an example of the type of dashboard I build. 

https://app.powerbi.com/view?r=eyJrIjoiM2MwNjU0NWItZmY4Zi00OTNlLWExZGMtMDkxMTc2MDk3MTY5IiwidCI6IjY1M...

You can see on page 6 there is a Patients in the Department tab (PID) but this is only sliceable by the date+hour in the table imported from excel and is separate from all the other slicers (Age/acuity etc). I would like to do this measure within PBI using the base data in the EDTable so I can slice it with the other patient data. Many thanks for reading a very long explination!! 

[v-caliao-msft]

@Funkmiester,

You need to have a date time table, and then calculate active users.

DateTime table

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

Create a calculated column to return active users.

ActiveUsers = CALCULATE(COUNT(EDTable[AttendanceID]),FILTER(ALL(EDTable),(DateTime[Date]=EDTable[AttendanceDate]&&HOUR(EDTable[AttendDatetime])<=DateTime[Hour]&&HOUR(EDTable[DischargeDatetime])>=DateTime[Hour])||(DateTime[Date]=EDTable[AttendanceDate]&&DateTime[Date]<EDTable[DischargeDate].[Date])))

Regards,

Charlie Liao

[Funkmiester]

Dear Charlie,

first of all thank you, very,very helpful!

I understand what it is and how it works. It will produce a table of all active patients in the hours.

What I can't do is use the slicers in the EDTable to change the content. (EG look at only those who arrived by ambulance in the ArrivalMode column in the EDTable)

I have tried to connect the new table to the EDTable using a shared field of Date:Hour. They connected Didn't work. Any way of 

I tried adding additional fields to the filter context and that worked but that would mean I would need an additional table column for all the permutation that I need (several dozens). That would work but would be very slow when opening the sheet.

I've tried changing the ALL filter context but that didn't work either, is there a way of doing.

Is there a way to slice the new table from the EDTable?

or

Is there a way of creating a measure that sits within the EDtable that does the same function so I can slice on the EDtable fields?

Your help is very much appreciated,

*** for anyone copying the table script, the number "8" row is missing, easy to add.***

[Vvelarde]

@Funkmiester

Hi, lets try with this:

A New Table:

CalendarTable = CROSSJOIN(CALENDAR("01/01/2017","31/12/2017"),GENERATESERIES(0,23,1))

A measure:

Count-AttendanceID =
VAR Attendance =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Attendance Date] = SELECTEDVALUE ( CalendarTable[Date] )
                && Table1[Attendance Hour] <= SELECTEDVALUE ( CalendarTable[Hour] )
        )
    )
VAR Discharge =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Discharge Date] = SELECTEDVALUE ( CalendarTable[Date] )
                && Table1[Discharge Hour] <= SELECTEDVALUE ( CalendarTable[Hour] )
        )
    )
RETURN
    Attendance - Discharge

Let me know if works

Regards

Victor

Lima - Peru

[Funkmiester]

Once again thank you very much Victor,

I've tried it and have some very odd answers, I understand it and it should work.

I am in the process of de-bugging it and am trying it piece by piece comparing the measure outputs of the admission and discharge numbers with the ACTUAL numbers which are easy to get form tables of (Admission date/Admission Hour+Patient ID) and (Discharge date/Discharge Hour+Patient ID). The trick is to subtract one from the other on an ongoing basis. 

At the moment they show different numbers and I need to find our why, however it does change based on the slicers.

I will keep trying on this and keep posting as I hope others will benefit once it's cracked.

Thanks, and any other options welcome!

Paul

[Funkmiester]

Enclosed are some problem solving tables. The top right is the measure as provided and looks good but the numbers are wrong, the next two tables are the individual arrival and discharge tables as provided by the measure, they are alo incorrect. The two tables below are the actual counts of arrivals and discharges by counting the rows by dropping them into the table. I can't figure out why they are different. I've lookes through each hour of the day and manually counted the rows and the bottom tables are correct (Date in the row, hour in the column, count of Patient ID in the value). One for admission times, one for discharge time, What I can't figure out is why the measure isn't working, anyhelp would be appreciated. The numbers it creates don't make any sense relative to the actuals.