Forum Discussion

poojithas's avatar
poojithas
Helper III
3 years ago

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

    • poojithas's avatar
      poojithas
      Helper 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.

    • poojithas's avatar
      poojithas
      Helper 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))
      )

       

    • poojithas's avatar
      poojithas
      Helper III

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

    • poojithas's avatar
      poojithas
      Helper 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.

       

      KEYCT_DATESTATUSDurationDUETOTALEXPIRES% USEDLAST_REVIEW_DATENEXT_REVIEW_DATE
      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%  
                
  • some_bih's avatar
    some_bih
    Community 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.

     

     

    • poojithas's avatar
      poojithas
      Helper 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

       

       

       

  • some_bih's avatar
    some_bih
    Community Champion

    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

     

     

    • poojithas's avatar
      poojithas
      Helper III

      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.

      • some_bih's avatar
        some_bih
        Community Champion

        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.

  • some_bih's avatar
    some_bih
    Community Champion

    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