Flag in between 2 dates but show all values even without a date

[benfitz23]

Hello, 

I have a table with 3 columns name, open date and closed date. There is about 2000 rows in the data and majority won't have any date assoicated with them these rows will be blank. I basically want to show that if there is no open or closed date then a column shows 'PASS' if not a column shows 'FAIL'.

This is easy enough as it is the problem is I need to have a date filter where someone selects a date and if the date selected is between the open and closed date it shows 'FAIL' but any other date either before or after it shows 'PASS'. 

If there is no closed date then if the date selected is after the open date is shows 'FAIL' otherwise 'PASS'

and if there is no open or closed date it always shows 'PASS' 

When a date is selcted i need all data to appear not just those in that date filter

eg.

if this is my data and the date 02/02/2023 is selected then A,C,D,E,F will show 'PASS' and B will show 'FAIL'

I have the table ready I am just not sure what DAX formula may be helpful to use for this

Thank you for all your help

B

[Timuran · Accepted solution]

Hey @benfitz23 ,

Create a table for Date (use CALENDARAUTO or CALENDAR)

I did as follow :

Date = CALENDAR(DATE(2023,01,01), DATE(2024,01,01))

And create a Measure like this :

Pass / Fail = IF(SELECTEDVALUE('Table'[open]) >= MIN('Date'[Date]) && SELECTEDVALUE('Table'[open]) <= MAX('Date'[Date]) || SELECTEDVALUE('Table'[closed]) >= MIN('Date'[Date]) && SELECTEDVALUE('Table'[closed]) <= MAX('Date'[Date]), "PASS", "FAIL")

Then add a filter for the date (of your new created table)

Have a nice day.

[benfitz23]

Thank you so much! @Timuran when i filter by date the table below remains which is PERFECT! but the filter to filter by name disappears based on what date i have entered

is there any way to show all values in the name filter regardless if they fall in the date range or not?

[benfitz23]

nevermind i got it to work using Edit interactions in the format tab - thank you for your help @Timuran 

[Timuran]

@benfitz23 , Glad it's working well, have a nice day 🙂