Forum Discussion
Beginner, need help finding a solution to filter based on another date 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
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?
- danextian
Super User
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.
- Greg_Deckler
Community Champion
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.
