Forum Discussion
Helper IIIActive patients when slicing by date
I have been looking online and scouring everything looking for the answer to this and I am coming up short. One looked like it could help but the DAX was not validating on my system properly so I must be doing it wrong or looking for the wrong information so here is my problem:
I have a table of Patient Admissions "Admissions" With columns:
AdmitDate DischargeDate
Now, if the DischargeDate is null then they're active. That is an easy calculation to find the current active patients just count everyone who has an admission date after the date slicer but no discharge date.
However, What if I'm looking at (slicing by) the month of January 2022. They want to know everyone who was active in that month. That includes those who may have a discharge date before 1/31/2022.
Say, they had an admit date of 1/2/2022 and discharge 1/25/2022. Well, that would be counted as one. but I also need to know those who have no discharge date too, since they also would be active. BUT if their admit date was 2/1 they would not be counted.
How the heck do I make a measure or whatever on that? I can't seem to figure it out.
You likely want a measure pattern similar to this one. This assumes that you have a Date table and there is no relationship to from it to the table with your patient data. If you do have a relationship, you can add REMOVEFILTERS(PatientTable) into the CALCULATE.
Active employees =
VAR mindate =
MIN ( Date[Date] )
VAR maxdate =
MAX ( Date[Date] )
RETURN
CALCULATE (
COUNT ( Table[Employee] ),
AND (
Table[StartDate] <= maxdate,
OR ( Table[TerminationDate] >= mindate, ISBLANK ( Table[TerminationDate] ) )
)
)Pat
For Count refer to this:
Active employees = VAR mindate = MIN ( DateTable[Date] ) VAR maxdate = MAX ( DateTable[Date]) RETURN CALCULATE ( COUNT ( Admissions[PatientID] ), AND ( Admissions[AdmitDate] <= maxdate, OR ( Admissions[DischargDateFixed] >= mindate, ISBLANK ( Admissions[DischargDateFixed] ) ) ) )However, this is the same thing but rewritten which mahoneypat proposed earlier. That works, so kudos to this man.
7 Replies
- mahoneypat
Microsoft Employee
You likely want a measure pattern similar to this one. This assumes that you have a Date table and there is no relationship to from it to the table with your patient data. If you do have a relationship, you can add REMOVEFILTERS(PatientTable) into the CALCULATE.
Active employees =
VAR mindate =
MIN ( Date[Date] )
VAR maxdate =
MAX ( Date[Date] )
RETURN
CALCULATE (
COUNT ( Table[Employee] ),
AND (
Table[StartDate] <= maxdate,
OR ( Table[TerminationDate] >= mindate, ISBLANK ( Table[TerminationDate] ) )
)
)Pat
- vojtechsima
Super User
Hi, Thomas_MedOne,
I did a short demo for you with dynamic usage, if you want to do the same for any month later using slicer:First Table is affected by First Slicer, thus January, Second Table is affected by Second Slicer, thus February and the Third Table contains all patients without any filter. Please note that I use Date Table without any relationship and I added an extra column DischargeDateFixed that changes null to a way later date.
Here is the measure:BelongsToMonthMeasure = var _endOfMonth = MAX(DateTable[End of Month]) var _startOfMonth = MAX(DateTable[Start of Month]) var _IntervalCheck = if(MAX([AdmitDate]) <= _endOfMonth && MAX(Admissions[DischargDateFixed]) >= _startOfMonth, "Active") return _IntervalCheck
The code suggests you want to see the whole month, but you can adjust the code that it will take the min and max date from the slicer, you will only need to change the "_startOfMonth" to "MIN" and for both variables change Field to "Date" instead of "End/Start Of Month".
I am also adding the Date Table, so you have the full picture: 
Thomas_MedOnevojtechsima mahoneypat Do your solutions provide for not just the whole month but selecting a sub-set like a week or just one day?
I have a Date Table setup and in it, I have slicers for month only, Week, and day. here is a screenshot of those slicers.
- vojtechsima
Hi, Thomas_MedOne
If you adjust the solution as I mentioned in the comment, it should work:BelongsToIntervalhMeasure = var _endOfInterval = MAX(DateTable[Date]) var _startOfInterval = MIN(DateTable[Date]) var _IntervalCheck = if(MAX([AdmitDate]) <= _endOfInterval && MAX(Admissions[DischargDateFixed]) >= _startOfInterval, "Active") return _IntervalCheck- Thomas_MedOne
This tells me if they're active or not. How do I then get a count?