cancel
Showing results for
Did you mean:
Helper II

Excluding after date using slicer

Hi,

I have a scenario where I have patients who are in a care facility. These patients get various assessments done during their stay such as weight, BMI, blood pressure, cholesterol etc. I display these assessments during a specific month using a slicer which has months like Jan 23, Feb 23 etc to show assessments in Jan 23 or Feb 23 etc.

However, patients can be discharged or they may pass away and at this time their file is closed on the system. This is recorded in a datetime field called FileClosed in my Patients table.

When a patient is no longer in the facility, I don't want to show them. Eg if a patient was discharged on 15 Jan 2023, then in the Jan 23 slicer period, I DO want to see them (they were still a patient in Jan 23 and earlier months), but I don't want to see them in Feb 23.

I am struggling to figure out how I can show a patient before he/she died or was discharged, but exclude them after that point.

Thanks for guidance.

1 ACCEPTED SOLUTION
Helper II

I have solved this another way. What I did was to create a calculated column as follows:

Active = If(calculate(Values(Assessment_Period[EndDate]),
filter(Assessment_Period, Assessment[Months] = Assessment_Period[Months])) > EOMONTH(calculate(Values(Patient[FileClosed]),
filter(Patient, Assessment[PatientId] = Patient[Id])), 0), "No", "Yes"
)

This determines at the row level in the assessment table if that particular assessment is for a patient who is no longer active.
3 REPLIES 3
Helper II

I have solved this another way. What I did was to create a calculated column as follows:

Active = If(calculate(Values(Assessment_Period[EndDate]),
filter(Assessment_Period, Assessment[Months] = Assessment_Period[Months])) > EOMONTH(calculate(Values(Patient[FileClosed]),
filter(Patient, Assessment[PatientId] = Patient[Id])), 0), "No", "Yes"
)

This determines at the row level in the assessment table if that particular assessment is for a patient who is no longer active.
Solution Sage

I am not sure because of lack of data. But I think following these steps would solve your problem:

1-Create a measure as below:

```PatientFilter =
SUMX (
FILTER (
Assesment,
YEAR ( RELATED ( Patient[FileClosed] ) ) * 12
+ MONTH ( RELATED ( Patient[FileClosed] ) )
= YEAR ( MAX ( DateTable[Date] ) ) * 12
+ MONTH ( MAX ( DateTable[Date] ) )
|| ISBLANK ( RELATED ( Patient[FileClosed] ) )
|| RELATED ( Patient[FileClosed] ) >= MAX ( DateTable[Date] )
),
1
)```

2-Then Put this measure as a filter on your visual in filterpane. (set it to advanced filter, IS GREATER THAN 0).

**Notice that at first your visual shows no value, But once you put date slicer from date table and select a date, it would work and shows the related patients.

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

Solution Sage

Would you please clear the relationships between tables? Which columns are related and also what is DateTo column in assesment table?

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors