Forum Discussion
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
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.
2 Replies
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.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.
