Forum Discussion
Date Filters with NULL values
Hi I am trying to create a report where, I need to filter by start date and end date, there are few record with NULL data in start and end date column. These start dates and end dates are...
Please refer to the link below.
- Create two tables.
StartDateSlicer = CALENDAR(DATE(2015,1,1),DATE(2016,6,30))
EndDateSlicer = CALENDAR(DATE(2016,7,1),DATE(2017,12,31)) - Use the date in new create tables in your startdate and enddate slicer.
- Create a measure in your originale table
Measure =
var minstartdate = MIN(StartDateSlicer[Date])
var maxstartdate = MAX(StartDateSlicer[Date])
var minenddate = MIN(EndDateSlicer[Date])
var maxenddate = MAX(EndDateSlicer[Date])
return
if((ISBLANK(MAX(Employee[StartDate]))||ISBLANK(MAX(Employee[EndDate]))||((MAX(Employee[StartDate])>=minstartdate&&MAX(Employee[StartDate])<=maxstartdate)&&MAX(Employee[EndDate])>=minenddate&&MAX(Employee[EndDate])<=maxenddate)),1,0) - Use this measure in your visual as a filter.
Regards,
Charlie Liao
v-caliao-msft
Thanks for your response, but the filters are not working properly. The problem I am facing is, when we have a default date range in filter it considers NULL values also, but as soon as we change the date to desired dates, it removes NULL immediately. Is there any way that we can include NULL even if we filter the dates to desired dates.
Or can we have option in Date Filter Range which specifies it includes NULL.
Kindly suggest what to do.
Regards,
Sahil
This is a glaring problem with the slicers, in my opinion. Blank / NULL / missing dates are a legitimate value. The ability to include hem with a selected date range is essential.
