Forum Discussion

jilanis's avatar
jilanis
Regular Visitor
4 years ago
Solved

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

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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's avatar
      jilanis
      Regular Visitor
      4 years ago

      Thank you. Much appreciated v-jingzhang.

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