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 Dev Camp Session 26

Check it Out!

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

Top Solution Authors