cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Meena
Frequent Visitor

Ignore filter based on another filter

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

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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"
    )

vbinbinyumsft_0-1664243745725.png

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.

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-binbinyu-msft
Community Support
Community Support

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"
    )

vbinbinyumsft_0-1664243745725.png

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.