DAX measure to exclude the rows of table visualization using Date Slicer?

[deb_power123]

Hi Everyone, 

I have a table say Student table. I have the below columns in the table:  

StudentID

StudentName

AdmissionDate

LastAttendanceDate

DateLeft

Location

Studenttype

I created another calendar table with a range of dates on Dates column and is related it to the Last AttendanceDate column of the Student table by many:1 relation.

As per my scenario: If the maximum date range selected in date slicer is greater than the "Date Left" column value of the Student table  then exclude that row from the table visual. For example :In the above case my maximum date selected is "15.05.2011" then the date left column has one date 14.05.2011 which is less than maximum selected date so this row should be filtered out and excluded from the table visual. Below is the image of my visualization:-

I need to write a DAX measure formula to handle this and exclude the rows ,please provide your suggestions. Appreciate for all your inputs. I wrote the below measure using Filter expression but it didn’t work.

 I think I did something incorrect as I am not fluent in using FILTER function]

 M1 =  IF (SELECTEDVALUE 'Calendar'[Dates]>=MAX('Calendar'[Dates] ,FILTER(ALL('Student),'Student'[DateLeft]<=MAX('Calendar'[Dates])

My Calendar Table data :

Dates

01-05-2011
02-05-2011
03-05-2011
04-05-2011
05-05-2011
06-05-2011
07-05-2011
08-05-2011
09-05-2011
10-05-2011
11-05-2011
12-05-2011
13-05-2011
14-05-2011
15-05-2011

 My Input excel data is as below :

StudentID	StudentName	AdmissionDate	LastAttendanceDate	DateLeft	Location	Studenttype
100	Harry	01-02-2010	10-05-2011 10:45	01-06-2011	London	FullTime
101	Sally	01-02-2010	01-05-2011 16:45		London	FullTime
103	Mary	01-02-2010	02-05-2011 12:45		London	FullTime
104	John	01-02-2011	07-05-2011 09:45	31-09-2011	Paris	FullTime
105	Geeta	01-04-2011	13-05-2011 12:45		Athens	FullTime
106	James	01-02-2011	02-05-2011 12:45		Athens	FullTime
107	Marc	01-02-2010	11-05-2011 12:45	14-05-2011	Athens	PartTime
108	Sam	01-04-2011	02-05-2011 14:45	02-10-2011	Toronto	PartTime
109	Ravi	01-04-2010	04-05-2011 12:45		Toronto	PartTime
110	Jose	01-02-2011	08-05-2011 15:45	03-08-2011	Tokyo	FullTime

 My Expected Output as below :[the one striked out should be removed since the maximum value of date selected is 15.05.2011 in date slicer and the DateLeft is 14.05.2011 which is less than selected date.so that row should be excluded and filtered out from below table visualization. Since rest all values of Date left are greater than the Maximum date range selected in date slicer.

Regards

Sameer

[dedelman_clng]

Hi @deb_power123  - 

Thank you for the detailed explanation of your problem. Please try these steps and see if it works for you.

1) Check the "Student" data - you have "Sept 31" as a "Date Left" and there is no such date. You should also consider removing the time values from Last Attendance Date as "1 May 2010 12:45 PM" is not the same as "1 May 2010" and the relationship with the Calendar won't work correctly.

2) Create a measure determining whether or not to show a row based on DateLeft 

ShowRow =
IF (
    ISBLANK ( MIN ( Students[DateLeft] ) )
        || NOT ( MIN ( Students[DateLeft] ) IN ALLSELECTED ( 'Calendar' ) ),
    1,
    0
)

(NOTE: this measure is designed with the assumption that it will always be calculated in a context containing only one student record - if you start to group records it may behave differently)

3) Add "ShowRow" to the visual filters, and set "Show Items when the Value is" to 1

Hope this helps

David