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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors