cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jilanis
New Member

PBI filter: Include Null/Blank with Date From

Dear all,

 

I am trying to set up a report with a colleague and we stuck on this problem. We are able to apply filter based on first two conditions but third condition is proving to be a challenge i.e where [Closure_Date] is also Null:

 

Field_1Field_2
Closure_DateStarting_Date
  
Filter on PBI: 
Date-FromDate-To
01/04/202131/03/2022

 

 

Requirement:

Data Table to be filtered based on:
Starting_Date is < = [Date-To]
Closure_Date is  > = [Date-From] OR [Closure_Date] is Null/blank

 

Any help or advice would be greatly appreciated. Thank you.

Much appreciated.

Regards

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @jilanis 

 

Are you going to add filters in a calculation or filter data displayed in a visual? If you want to add filters in a DAX expression, you can try below mode

Measure =
CALCULATE (
    [Expression],
    FILTER (
        'Table',
        'Table'[Starting_Date] <= SELECTEDVALUE ( 'Field1'[Date-From] )
            && (
                'Table'[Closure_Date] >= SELECTEDVALUE ( 'Field2'[Date-To] )
                    || ISBLANK ( 'Table'[Closure_Date] )
            )
    )
)

 

If you want to filter data in a visual, you can create the following measure as a flag. Use this measure as a visual-level filter on the visual and set it to show items when value is 1. 

flag =
IF (
    SELECTEDVALUE ( 'Table'[Starting_Date] )
        <= SELECTEDVALUE ( 'Field1'[Date-From] )
        && (
            SELECTEDVALUE ( 'Table'[Closure_Date] ) >= SELECTEDVALUE ( 'Field2'[Date-To] )
                || ISBLANK ( SELECTEDVALUE ( 'Table'[Closure_Date] ) )
        ),
    1,
    0
)

 

For above methods, your "Date-From" and "Date-To" should come from two independent date tables which don't have relationships with other tables. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @jilanis 

 

Are you going to add filters in a calculation or filter data displayed in a visual? If you want to add filters in a DAX expression, you can try below mode

Measure =
CALCULATE (
    [Expression],
    FILTER (
        'Table',
        'Table'[Starting_Date] <= SELECTEDVALUE ( 'Field1'[Date-From] )
            && (
                'Table'[Closure_Date] >= SELECTEDVALUE ( 'Field2'[Date-To] )
                    || ISBLANK ( 'Table'[Closure_Date] )
            )
    )
)

 

If you want to filter data in a visual, you can create the following measure as a flag. Use this measure as a visual-level filter on the visual and set it to show items when value is 1. 

flag =
IF (
    SELECTEDVALUE ( 'Table'[Starting_Date] )
        <= SELECTEDVALUE ( 'Field1'[Date-From] )
        && (
            SELECTEDVALUE ( 'Table'[Closure_Date] ) >= SELECTEDVALUE ( 'Field2'[Date-To] )
                || ISBLANK ( SELECTEDVALUE ( 'Table'[Closure_Date] ) )
        ),
    1,
    0
)

 

For above methods, your "Date-From" and "Date-To" should come from two independent date tables which don't have relationships with other tables. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Thank you. Much appreciated @v-jingzhang.

I understand the concept of both solutions. We will give it a go. Thanks again. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors