Forum Discussion
Filter pane multiple criteria for single column
Customer requirement for date filtering is closed date between 04/01/2023 and 04/30/2024 or closed date is blank. The closed date between values will change based on reporting period and other requirements so it cannot be hard coded. I have tried to have two closed date entries in the filter pane but which ever filter criteria is first over rides the second e.g. date between XX/XX/XXXX and YY/YY/YYY removes blanks. Is there a way to right a DAX that could take input from a date slicer and incorporate the "or blank" statement to filter the data?
try this measure and see if it works.
Date Filter =
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'[Closed Date]),
'Table'[Closed Date] >= MinDate && 'Table'[Closed Date] <= MaxDate || ISBLANK('Table'[Closed Date])
)
)
If you have a Date table you can use the date field for there in this measure. and for the closed date use the date from the table which has the closed date and replace the values accordingly.
Please do let me know if this solution works.
4 Replies
try this measure and see if it works.
Date Filter =
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'[Closed Date]),
'Table'[Closed Date] >= MinDate && 'Table'[Closed Date] <= MaxDate || ISBLANK('Table'[Closed Date])
)
)
If you have a Date table you can use the date field for there in this measure. and for the closed date use the date from the table which has the closed date and replace the values accordingly.
Please do let me know if this solution works.- audreygerred
Super User
You can achieve the between portion by using your date field in the filter pane and changing the filter type to Advanced filtering instead of basic - then your users can set the dates for the after and before portions. This only allows for two conditions - to also account for the blank you could try have two filters in the filter pane with Advanced filtering: in the first one do Show items when the value is blank or is on or after (or after depending on what you want to do) and then pick a date. Then, in the second one, Show items when the value is on or before (or is before depending on how you want to do it) and put the end date that you want. The first filter should get you all teh blanks and everything on or after or after that first date you put. The second filter will further limit it so that it isn't EVERYTHING after the date given in the first filter.
My sample data doesn't have any blank dates, but the below still helps to show what I did:
