'OR' include blank values in date slicer

[rigelng1995]

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   

[amitchandak · Accepted solution]

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-tr...

Appreciate your Kudos.

[rigelng1995]

Hey @amitchandak 

Thanks for the reply, I am having trouble understanding the "slicer[allowblank]" part.

Any chance you can attach a demo pbix files?

Thanks

[amitchandak]

slicer[allowblank] is for checking, weather blank is allowed on not.

[Anonymous]

Hi ,

Can you please guide me what the "Slicer[Allowblan] is refering to? I can see you have mentioned it is to see weather blank is allowed on not. but this is a column in our table? or a slicer? not sure how to get this.