Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DataSkills
Helper III
Helper III

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. 

 

Relationship.jpg

 

Thanks for guidance. 

1 ACCEPTED SOLUTION
DataSkills
Helper III
Helper III

Hello @MohammadLoran25 thank you for your suggestion. 

 

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. 

View solution in original post

3 REPLIES 3
DataSkills
Helper III
Helper III

Hello @MohammadLoran25 thank you for your suggestion. 

 

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. 
MohammadLoran25
Super User
Super User

@DataSkills ,

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

MohammadLoran25
Super User
Super User

Hi @DataSkills ,

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.