Ignore filter based on another filter

[Meena]

Hello All,

I am new to power BI and working on an issue. My dataset is as below and have a filter on ClosedDate to include only Dates in Future. I have to ignore the ClosedDate filter if the Status is Ongoing. Please help.

ID                         Status               ClosedDate               filter on ClosedDate (now)

100                      Initial                   01/01/2025             Return   

101                     Ongoing               01/01/2021            --- this is also expected to Return as status is 'Ongoing'

103                     Paused                 01/02/2023             Return

104                     Ongoing               01/02/2023            Return 

105                     Closed                01/01/2021

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the ClosedDate column of the Data Table to the Date column of the Calendar Table.  Create a Table visual and drag ID/Status in there.  Write this measure and drag it to the visual

Date value = min(Calendar[Date])

Measure = calculate(countrows(Data),filter(values(Calendar[Date]),([Date value]>today()&&Data[Status]<>"Closed")||Data[Status]="Ongoing"))

Hope this helps.

[v-binbinyu-msft · Accepted solution]

Hi @Meena ,

Please try below dax formula to create a measure and add it to table visual

Measure =
VAR cur_date =
    NOW ()
VAR cur_status =
    SELECTEDVALUE ( 'Table'[Status] )
VAR cur_closedate =
    SELECTEDVALUE ( 'Table'[ClosedDate] )
RETURN
    SWITCH (
        TRUE (),
        cur_closedate > cur_date, "Return",
        cur_status = "Ongoing", "Return"
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.