Forum Discussion
Beginner, need help finding a solution to filter based on another date column.
Hi all, bit of a newbie here doing his best after our main data guy just left and am here to pick up the peices.
I have a list in power BI Desktop and have been requested to filter a column that seemingly can't be done with jsut the basic fitler abilities in the visuals.
Here are the two columns in question:
I am trying to filter column "Status" to show everything, except to exclude the value "Closed" if the corresponding date in the "Submitted Date" column is older than 30 days from today's date.
I'm sure this will have to be a Dax statement. Consulting GPT led to to this formula to create a new table with an added column:
ejethan123 Did you create it as a column or a measure? It needs to be a measure and you need to use it in a visual where the measure has the context for each associated date. If you want a column you would do this:
Selector Column = VAR __Date = [Submitted Date] VAR __Status = [Status] VAR __Result = IF( __Status = "Closed" && __Date < TODAY() - 30, 0, 1 ) RETURN __Result
6 Replies
ejethan123 You want a Complex Selector. https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
Probably along the lines of:
Selector Measure = VAR __Date = MAX( 'Table'[Submitted Date] ) VAR __Status = MAX( 'Table'[Status] ) VAR __Result = IF( __Status = "Closed" && __Date < TODAY() - 30, 0, 1 ) RETURN __ResultAdd that and filter for only 1's
Greg_Deckler Appreciate it! It seems close to working. Currently though every item marked as "closed" is getting a 1 including those with a date older than 30 days. Could it be because since there exists a date newer than 30 days for the "closed" value, that it is applying a 1 to all closed values?
Hi ejethan123
This part of Greg's logic must be met for 0 to be returned
__Status = "Closed" && __Date < TODAY() - 30You're right. If there are rows with a closed status and dates earlier than 30 days, they will return 1. To return 0 for those with a closed status or dates older than 30 days, change && to ||
__Status = "Closed" || __Date < TODAY() - 30Sorry for the confusion. What I am trouble shooting now is that everything returns a 1, even those with status "Closed" and dates older than 30 days, which is what I am trying to filter out.
ejethan123 Did you create it as a column or a measure? It needs to be a measure and you need to use it in a visual where the measure has the context for each associated date. If you want a column you would do this:
Selector Column = VAR __Date = [Submitted Date] VAR __Status = [Status] VAR __Result = IF( __Status = "Closed" && __Date < TODAY() - 30, 0, 1 ) RETURN __ResultGreg_Deckler For some reason the measure was returning all 1s in the visual. I made a column though as you suggested above and filtered by it and just hit it from the visual and it worked as intended! Thanks for the help. Excited to continue learning.
