topic Re: Include empty date values in table when changing date slider in DAX Commands and Tips

Include empty date values in table when changing date slider

poojithas — Mon, 05 Jun 2023 07:58:12 GMT

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.

Re: Include empty date values in table when changing date slider

some_bih — Mon, 05 Jun 2023 10:36:07 GMT

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

Re: Include empty date values in table when changing date slider

poojithas — Mon, 05 Jun 2023 11:13:13 GMT

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.

Re: Include empty date values in table when changing date slider

some_bih — Mon, 05 Jun 2023 11:36:24 GMT

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

Re: Include empty date values in table when changing date slider

poojithas — Mon, 05 Jun 2023 12:06:39 GMT

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)) )

Re: Include empty date values in table when changing date slider

some_bih — Mon, 05 Jun 2023 12:26:29 GMT

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

Re: Include empty date values in table when changing date slider

poojithas — Mon, 05 Jun 2023 13:09:47 GMT

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

Re: Include empty date values in table when changing date slider

some_bih — Mon, 05 Jun 2023 14:05:09 GMT

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

Re: Include empty date values in table when changing date slider

poojithas — Mon, 05 Jun 2023 15:24:28 GMT

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%

Re: Include empty date values in table when changing date slider

some_bih — Mon, 05 Jun 2023 21:41:19 GMT

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.

Re: Include empty date values in table when changing date slider

poojithas — Tue, 06 Jun 2023 01:29:01 GMT

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

Re: Include empty date values in table when changing date slider

some_bih — Tue, 06 Jun 2023 08:36:58 GMT

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

Re: Include empty date values in table when changing date slider

some_bih — Tue, 06 Jun 2023 08:36:59 GMT

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

Re: Include empty date values in table when changing date slider

poojithas — Sat, 10 Jun 2023 06:14:29 GMT

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.

Re: Include empty date values in table when changing date slider

some_bih — Sat, 10 Jun 2023 06:47:45 GMT

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.

Re: Include empty date values in table when changing date slider

Manjula — Thu, 10 Oct 2024 16:18:05 GMT

poojithas is this resolved , i am also facing same issue