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
sahilhira162
Advocate I
Advocate I

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.

Capture3.PNG

These start dates and end dates are automatically set, as they pick the min and max dates. This is also showing the NULL records, but if I see the reports, its confusing, coz it looks like that its showing data only between those 2 dates.
Is there any way where I can changes in dates too NULL so that its clear that its starting from NULL to a particular date.

 

Any help is appreciated, thanks in advance.


Regards,
Sahil

7 REPLIES 7
v-caliao-msft
Employee
Employee

@sahilhira162,

 

Please refer to the link below.

  1. Create two tables.
    StartDateSlicer = CALENDAR(DATE(2015,1,1),DATE(2016,6,30))
    EndDateSlicer = CALENDAR(DATE(2016,7,1),DATE(2017,12,31))
  2. Use the date in new create tables in your startdate and enddate slicer.
  3. 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)
  4. Use this measure in your visual as a filter.
    Capture.PNG
  5. Capture2.PNG

 

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.

Greg_Deckler
Super User
Super User

Can you filter the null's out of your data? Or, can you replace the nulls in your data load with something like 1/1/1900?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

Thanks for your response.

No I can't filter them or change them to 1990. Some of the projects are just announced and start date, end date are not yet decided for them. But I want to see those projects in my report. I was confused, how can I make it visible in my report that it includes those NULL data as well. Coz what looks from the date filter is that the report contains data only from particular range only. I was hoping that in place of starting date, it can be blank so that user can understand easily that it contains NULL values as well.

Regards,
Sahil 

Add a text box "Includes Null Values"?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Any other alternative.?

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.