Getting DAX error: DAX doesn't support comparing values of type Text with type Date

Hi All,

I have a Student table with columns:-

I have a calendar table connected to student table by 1:n relation.

I want to exclude all the rows when the Maxiumum selected value of [Datescolumn of the calendar table]in the date slicer is greater than the Date Left column of the student Table.

 

My DAX measure is as below :-

Visual exclude =

IF(

SELECTEDVALUE('student'[DateLeft])<MAX('Calendar'[Dates]),

1,0

)

 

But when I select this DAX measure it resolves into an error message in powerBI desktop as below.

 

Expected output: This should show the flag set to 1 or 0 in the table matrix column for the "visual exclusion". I can then add this measure in the visual filter to remove all rows from table visualization which fall under the given condition.

 

My input 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

 

I used CALENDARAUTO() function for calendar table to generate date range :-

 

How can I modify the DAX to handle the above error-message or please suggest any other approach to achieve the expected output?

 

Appreciate for all your inputs.

 

Regards

Sameer