Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Anonymous
Not applicable

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/
Anonymous
Not applicable

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!