Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

'OR' include blank values in date slicer

Hi there

 

I have a table with 3 columns , SaleDate, ReceiveDate, and SaleAmount. 

 

SaleDate is not null by default, yet ReceiveDate can accept blank values. 

 

How do I create slicers so that the end users can filter for a certain ReceiveDate and then decide whether to include blanks or not. 

 

In other words. I want the following -

formula in slicer --- or ((ReceiveDate >= Slicermin and ReceiveDate <= Slicermax), isblank(ReceiveDate))

 

As shown in the screen shot below -  I want to be able to select from the 3rd of Jan until the 4th of Jan AND including blanks

 

Also found an interesting interaction while playing around with the slicer  - it seems that if you have blank values in the date column (ReceiveDate) the left most part of the slider actually represents the blank values. 

 

 

Thanks

Rigel   

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Date should not be joined with receive or uses cross join if have active join

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

    For use relation and crossfilter refer :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

    Appreciate your Kudos.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Date should not be joined with receive or uses cross join if have active join

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

    For use relation and crossfilter refer :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        is for checking, weather blank is allowed on not.

  • Vaish1's avatar
    Vaish1
    Frequent Visitor
    1 year ago

    I have this table, where i want a date slicer where blank should be always included with the option of in between filter.