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 ...
  • v-jingzhang's avatar
    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.