Forum Discussion
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.
15 Replies
- some_bihCommunity Champion
Hi poojithas check this features in Power BI https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data I hope this help
- poojithasHelper III
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.
- some_bihCommunity Champion
Hi poojithas please check community link https://community.fabric.microsoft.com/t5/Desktop/OR-include-blank-values-in-date-slicer/m-p/959529 I hope this help
- poojithasHelper III
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()) return 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)) )
- poojithasHelper III
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.
KEY CT_DATE STATUS Duration DUE TOTAL EXPIRES % USED LAST_REVIEW_DATE NEXT_REVIEW_DATE 200635194 6/15/2022 0:00 Active 7 0 109551.19 5/30/2023 0:00 73.03% 601139617 6/15/2004 0:00 Active 14 0 1510.28 75.51% 601484357 7/12/2010 0:00 Active 21 0 36857.27 8/28/2022 0:00 73.71% 2/28/2023 0:00 601524082 5/5/2011 0:00 Active 21 243.91 396.13 99.03% 601682696 4/29/2014 0:00 Active 21 0 27885.88 79.67% 2/28/2023 0:00 601733618 11/6/2014 0:00 Active 21 0 17637.81 70.55% 6/30/2022 0:00 601970197 5/23/2017 0:00 Active 14 0 18503.89 74.02% 5/31/2022 0:00 601981626 6/21/2017 0:00 Active 14 0 39572.5 11/7/2022 0:00 79.15% 2/28/2023 0:00 602075015 3/1/2018 0:00 Active 14 0 33399.61 74.22% 9/16/2022 0:00 1/31/2023 0:00 602075283 3/5/2018 0:00 Active 14 0 18270.34 73.08% 3/31/2023 0:00 602304971 9/10/2019 0:00 Active 14 0 21700.97 86.80% 1/31/2023 0:00 602322006 11/18/2019 0:00 Active 21 0 63385.44 97.52% 8/31/2022 0:00 602342303 3/2/2020 0:00 Temporary Close 14 0 5105.68 102.11% 7/31/2021 0:00 602357379 5/15/2020 0:00 Permanent Close 21 38524.93 75293.37 5/23/2022 0:00 100.39% 3/31/2022 0:00 602373348 8/24/2020 0:00 Active 14 0 1509.05 75.45% 6/30/2022 0:00 602424344 5/24/2021 0:00 Active 21 0 59300.76 6/30/2022 0:00 79.07% 602456321 10/13/2021 0:00 Permanent Close 14 2193.95 2263.95 113.20% 602469194 12/14/2021 0:00 Active 14 0 1408.9 70.45% 11/30/2022 0:00 3/31/2023 0:00 602481285 2/17/2022 0:00 Active 14 0 3536.07 70.72% 3/31/2023 0:00 602522226 8/2/2022 0:00 Temporary Close 21 84255.57 84290.57 1/13/2023 0:00 84.29% 602530958 9/2/2022 0:00 Permanent Close 14 1963.21 2033.21 101.66% 602532487 9/5/2022 0:00 Active 21 0 36069.97 72.14% 602540988 10/7/2022 0:00 Active 14 0 4129.52 82.59% 602543114 10/13/2022 0:00 Active 14 0 3656.91 73.14% 602548148 11/8/2022 0:00 Active 14 0 3657.48 73.15% 602561281 1/18/2023 0:00 Active 14 0 1412.03 70.60% 602564002 2/1/2023 0:00 Permanent Close 14 2045.93 4262.6 85.25% 602568822 2/27/2023 0:00 Permanent Close 14 4426.01 4461.01 89.22% 602568871 2/27/2023 0:00 Permanent Close 14 2216.08 2286.08 114.30%
- some_bihCommunity Champion
Hi poojithas , check 1) Uploading files on the link 1) Uploading files https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
- some_bihCommunity Champion
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.
- poojithasHelper III
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