cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anileshknpowerb
Helper III
Helper III

Need help on date range slicer

Hello experts,

I have following reporting requirement.

 

Below are my slicers:

Note : Completed Date is NULL for Status='New' and Status='Rejected'


Status                       Completed Date(Date between)
Approved                    02/01/2021 03/02/2021
New                                     NULL

Rejected                               NULL


Table visual:

Name      Status                    Completed Date
Rec1        Approved                 02/10/2021

Rec2        Approved                 02/18/2021   

Rec3        Approved                 02/23/2021   

Rec4        Approved                 03/01/2021   
Rec5        Rejected                    NULL    

Rec6        Rejected                    NULL

Rec7        New                          NULL

Rec8        New                          NULL


The 'Status' slicer is Multiselect enabled. The end user want to see records for all Status and when he choose the date range from 'Completed Date' slicer, he want to see records range between the date selected in 'Completed Date' slicer for 'Approved' status and he want to see records for 'New' and 'Rejected' status as well.

 

For example, If user chooses all Status and chooses Completed Date betwen '02/20/2021' and '03/02/2021' , he should see the below data.

 

Expected output:

Table visual:

Name      Status                    Completed Date

Rec3        Approved                 02/23/2021   

Rec4        Approved                 03/01/2021   
Rec5        Rejected                    NULL    

Rec6        Rejected                    NULL

Rec7        New                          NULL

Rec8        New                          NULL

 

The condiction to show data is:

Where
(Status == 'Approved' AND Completed Date > 02/20/2021 AND Completed Date < 03/02/2021)
OR
(Status == 'Rejected')
OR
(Status == 'New')

 

The issue:

When I choose any date range in 'Completed Date' slicer, it shows data for only records that have dates and doesn't show which dont have dates:

 

Output I ma getting :

Table visual:

Name      Status                    Completed Date

Rec3        Approved                 02/23/2021   

Rec4        Approved                 03/01/2021   

 

How to get expected output?

 

Regards,

Anilesh

1 ACCEPTED SOLUTION

Hi @anileshknpowerb,

 

You can not filter null values in Between mode:

v-xulin-mstf_0-1615433440605.png

Here is a workaround is to replace the null values with virtual dates, such as any date that qualifies.

 

Best Regards,

Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-xulin-mstf
Community Support
Community Support

Hi @anileshknpowerb,

 

You can drag a column which contains blank value into slicer.

v-xulin-mstf_0-1615362222147.png

v-xulin-mstf_1-1615362252480.png

 

Best Regards,

Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

The user requirement is to use dates between option in 'Completed Date' slicer. User want to see what are the Records fall between selected date ranges , along with records that have NULL values for Completed Date.

Hi @anileshknpowerb,

 

You can not filter null values in Between mode:

v-xulin-mstf_0-1615433440605.png

Here is a workaround is to replace the null values with virtual dates, such as any date that qualifies.

 

Best Regards,

Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

lbendlin
Super User III
Super User III

Did you enable the "Show items with no data"  setting?

Yes, but it didn't help

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors