Forum Discussion
census
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.***
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 - DischargeLet me know if works
Regards
Victor
Lima - Peru
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
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.