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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataSkills
Helper II
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. 

 

Relationship.jpg

 

Thanks for guidance. 

1 ACCEPTED SOLUTION
DataSkills
Helper II
Helper II

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 II
Helper II

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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