Forum Discussion

Funkmiester's avatar
Funkmiester
Advocate I
8 years ago

census

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 ...
Need Help
v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
8 years ago

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's avatar
    Funkmiester
    Advocate I
    8 years ago

    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's avatar
      Vvelarde
      Community Champion
      8 years ago

      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's avatar
        Funkmiester
        Advocate I
        8 years ago

        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