How to filter and exclude rows based on date slicer maximum date range?

[deb_power123]

Hi Everyone,

I have a scenario where I have a table say Student table. I have the following columns as below: 

StudentID

StudentName

AdmissionDate

LastAttendanceDate

DateLeft

Location

Studenttype

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

As per my scenario: If the maximum date range selected in date slicer is greater than the "Date Left" column value  then exclude that row from the visual else include it. 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.

I need to write a DAX measure formula to calculate this and exclude the row which fulfills the above criteria , please provide your suggestions.Appreciate for all your inputs.

I wrote the below mesaure using Filter expression but it didnt work.

My Calculated Measure which is not working is as below:[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 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 final visualization. Since rest all values of Date left are greater than the Maximum date range selected in date slicer.

Regards

Sameer

[v-yuaj-msft · Accepted solution]

Hi @deb_power123 ,

Have you forgotten to create a "calendar" table?

Measure = 

var x1=CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

return

IF(DATEDIFF(MAX('student'[LastAttendanceDate]),x1,DAY)<0,1,0)

Result:

I don't think it can display the rows containing LastAttendedDate as null values. It will make errors.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

I too feel the same.It is not possible.Anyways thanks for all the helpful points

[v-yuaj-msft]

Hi @deb_power123 ,

Is the following result what you want? The remaining dates are only 10-5-2021, 12-5-2021 and 14-5-2021.

If not, please share your desired result with me based on your sample table. 

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

so, if i select a date range, 02-05-2011 to 08-05-2011 in date slicer, I should be able to show  the below results in my table visualization :-

I tried to copy paste the  data here but the forum rules doesnt let me do so.I attached the.pbix file in above reply

[deb_power123]

Hi ,

Can I display the rows containing LastAttendedDate as null values for date range as selected in the date slicer?

My pbix file is uploaded here , you can download it by clicking the link below:-

https://www.mediafire.com/file/bxsr7ics5k3p9gc/Student.pbix/file

[v-yuaj-msft]

Hi @deb_power123 ,

Do you want to exclude rows that contain less than the maximum selected value and rows that contain empty values, right?

This is even eaiser. You can modify the measure as follows.

filter_noblank_test = 

var x1=CALCULATE(MAX('Calendar'[Dates]),ALLSELECTED('Calendar'[Dates]))

return

IF(MAX('Students'[DateLeft])>x1,1,0)

Then you can repeat the same steps as I mentioned before.

Result:

 

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

Thankyou Yuna for your reply.It is slighlty different, its my bad that i couldnt explain it well.

Let me give an example .

If you can see Mary has one blank record in Last attendance Date in the above table for 02.05.2011 so I want to get rid of that null value of date or exclude this null value of date when I select the date range in my date slicer as minimum date = 01.05.2011 and max date as 03.05.2011 so only three values should return and null values should be removed. 

Present visual shows something like this .

I want to get rid of the rows containing all those blank or null values for Last Attendance dates column..

Can we modify the above measure recommended by you to handle this scenario? Please suggest..

Regards

sameer

[v-yuaj-msft]

Hi @deb_power123 ,

There seems to be some errors in your description.

There are only 30 days in September and the relationship between "calendar"[Dates] and "Student"[LastAttendanceDate] should be 1:many.

Then you can do some steps as follows.

1. create a measure

filter_test = 

var x1=CALCULATE(MAX('Calendar'[Dates]),ALLSELECTED('Calendar'[Dates]))

return

IF(NOT(ISBLANK(MAX('Students'[DateLeft])))&&MAX('Students'[DateLeft])<x1,0,1)

2. drag it to the filter of the table visual and select "value is 1".

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

Thankyou Yuna for the response.Could you please let me know how can  I exlude all the rows having null values for "date left "which are falling under the minimum and maximum calendar selected date range?