Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rigelng1995
Frequent Visitor

'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

Screenshot_39.png

 

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. 

 

Screenshot_40.png

 

Thanks

Rigel   

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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.

 

 

 

is for checking, weather blank is allowed on not.

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.