Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Include empty date values in table when changing date slider

Hi All,

I have a field called "NEXT_REVIEW_DATE" which is of date datatype. I am using this field as a range slicer.


There are few records with empty  date value for "NEXT_REVIEW_DATE"  .

When ever a user changes the slider, they should be seeing the records wtith empty date as well along with the selected dates,.

Inital View with blank dates:




Now, in the eg. below when the user changes the date,the blank records are disappearing.They should be able to see data from 1/23/2022 to 3/1/2023 along with the blank entities.



Could someone please let me know how to acheive this.

Thank you.


Memorable Member
Memorable Member

Hi @poojithas I think that the best would be to change your slicer to vertical list.This solution ensure user interaction is properly applied together with Blank values. I hope this help




Sarajevo, Bosnia and Herzegovina
Memorable Member
Memorable Member

Hi @poojithas I think that the best would be to change your slicer to vertical list.This solution ensure user interaction is properly applied together with Blank values. I hope this help




Sarajevo, Bosnia and Herzegovina

@some_bih  : User is looking to have a date filter with range and a drop down may not be a best option for them as the no.of dates are too many.

Hi @poojithas I agree, still to provide dates in range and BLANK at same time the best is to use slicer on interaction to include blanks otherwise, there could be ambigious results.

Sarajevo, Bosnia and Herzegovina
Memorable Member
Memorable Member

Hi @poojithas I inserted into Excel your data and impor into Power BI.

During transformation in Power Query I choose data type for dates columns as : type date. Result is visible below. In table visual when I change date period, dates from period AND Blanks are visible.

Please try to change your data type to type date and check interaction with slicer. I hope this help.




Sarajevo, Bosnia and Herzegovina

Hi @some_bih : I have set the data types as in the report. Blank dates are visible only when the left side of the slider is unchanged and right side slider is changed.

We would like to see blank records when right or left slider is changed by user.
Inital View when the slider is unchanged:


View when right side slider is changed, We get blank dates as well:


View when left side slider is changed, blank values are disappeared





Memorable Member
Memorable Member

Memorable Member
Memorable Member

Hi @poojithas please share your pbi file. Link from community is not one I participated.

Sarajevo, Bosnia and Herzegovina

@some_bih  : As I am having limitations to access to upload files outside f client network, I am pasting sample data here.


NEXT_REVIEW_DATE is the field used in the slicer.
Sample screenshot:



Whe, I change the slider , along with the selected dates, I should also be able to get the records with no dates as well.


Hope these details will help to replicate the issue.


2006351946/15/2022 0:00Active70109551.195/30/2023 0:0073.03%  
6011396176/15/2004 0:00Active1401510.28 75.51%  
6014843577/12/2010 0:00Active21036857.278/28/2022 0:0073.71% 2/28/2023 0:00
6015240825/5/2011 0:00Active21243.91396.13 99.03%  
6016826964/29/2014 0:00Active21027885.88 79.67% 2/28/2023 0:00
60173361811/6/2014 0:00Active21017637.81 70.55% 6/30/2022 0:00
6019701975/23/2017 0:00Active14018503.89 74.02% 5/31/2022 0:00
6019816266/21/2017 0:00Active14039572.511/7/2022 0:0079.15% 2/28/2023 0:00
6020750153/1/2018 0:00Active14033399.61 74.22%9/16/2022 0:001/31/2023 0:00
6020752833/5/2018 0:00Active14018270.34 73.08% 3/31/2023 0:00
6023049719/10/2019 0:00Active14021700.97 86.80% 1/31/2023 0:00
60232200611/18/2019 0:00Active21063385.44 97.52% 8/31/2022 0:00
6023423033/2/2020 0:00Temporary Close1405105.68 102.11% 7/31/2021 0:00
6023573795/15/2020 0:00Permanent Close2138524.9375293.375/23/2022 0:00100.39% 3/31/2022 0:00
6023733488/24/2020 0:00Active1401509.05 75.45% 6/30/2022 0:00
6024243445/24/2021 0:00Active21059300.766/30/2022 0:0079.07%  
60245632110/13/2021 0:00Permanent Close142193.952263.95 113.20%  
60246919412/14/2021 0:00Active1401408.9 70.45%11/30/2022 0:003/31/2023 0:00
6024812852/17/2022 0:00Active1403536.07 70.72% 3/31/2023 0:00
6025222268/2/2022 0:00Temporary Close2184255.5784290.571/13/2023 0:0084.29%  
6025309589/2/2022 0:00Permanent Close141963.212033.21 101.66%  
6025324879/5/2022 0:00Active21036069.97 72.14%  
60254098810/7/2022 0:00Active1404129.52 82.59%  
60254311410/13/2022 0:00Active1403656.91 73.14%  
60254814811/8/2022 0:00Active1403657.48 73.15%  
6025612811/18/2023 0:00Active1401412.03 70.60%  
6025640022/1/2023 0:00Permanent Close142045.934262.6 85.25%  
6025688222/27/2023 0:00Permanent Close144426.014461.01 89.22%  
6025688712/27/2023 0:00Permanent Close142216.082286.08 114.30%  

Hi @some_bih - I have a sample test pbix file. Could you suggest how I can share the file here

Memorable Member
Memorable Member

Hi @poojithas please check community link I hope this help

Sarajevo, Bosnia and Herzegovina

@some_bih  - Thanks for the link but I am unable to fix the issue.  Below is the code from  the link provided.  I have tried to replace the [Date] field from the code with the [NEXT_REVIEW_DATE] in my case . 

Also, I was unable to get to how Slicer[Allowblank] is used. Could you please share a sample pbix file if possible.


measure =
var _min = minx(allselected(Date,Date[Date])
var _max = minx(allselected(Date,Date[Date])
var _maxval = if(isfiltered(Slicer[Allowblank]),max(Slicer[Allowblank]),blank())
if(_maxval ="Allowblank",
calculate(sum(table[Value]),filter(all(Table[Receive Date]),Table[Receive Date]>=_min && Table[Receive Date]>=_max && isblank(Table[Receive Date])))
calculate(sum(table[Value]),filter(all(date),Date,Date[Date]>=_min && Date[Date]>=_max))


Memorable Member
Memorable Member

Hi @poojithas check this features in Power BI I hope this help

Sarajevo, Bosnia and Herzegovina

Hi @some_bih  : I tried checking this option and I am still not able to see any data with null dates when the dates in the slicer is changed.

Please note that we do have data in the dataset for the missing dates, just that the date field is empty.

There are other fields as well in the report. The above is a sample data with 2 fields.

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors