PBI filter: Include Null/Blank with Date From

[jilanis]

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_1	Field_2
Closure_Date	Starting_Date
Filter on PBI:
Date-From	Date-To
01/04/2021	31/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

[v-jingzhang · Accepted solution]

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.

[jilanis]

Thank you. Much appreciated @v-jingzhang.

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