Need Slicer(s) to Filter Multiple Date Columns on Single Table Using "OR" Instead of "AND"

[Anonymous]

Hi - long time lurker- first time poster. This is one of the most well staffed, helpful, and insightful communities I've ever seen- so looking forward to putting you guys to work on this question.

I have a table with multiple date columns, thus I am using seperate slicers to filter each date column. However by default when multiple filters are applied via slicers, they use an AND. I need the filters to use OR. An ideal solution will also allow me to connect these together with a Date table and use a single slicer. 

In the below example. When the Date slicers are set 11/1/2018 - 11/30/2018

Current Functionality: Order 3,4

Required Functionality: Orders 2,3,4,5

Order #	ShipDate	PourDate
1	10/25/2018	10/29/2018
2	10/26/2018	11/1/2018
3	11/1/2018	11/2/2018
4	11/2/2018	11/3/2018
5	11/3/2018	10/19/2018

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Create a calendar date (which has no relationship with the order table)

add "Calendar Filter"[Date] in the date slicer, select 2018/11/1~2018/11/30

create  a measure in Order table

Measure = var min1= MIN('Calendar Filter'[Date]) var max1=MAX('Calendar Filter'[Date])
 
return IF((MAX([PourDate])>=min1&&MAX([PourDate])<=max1)||(MAX([ShipDate])>=min1&&MAX([ShipDate])<=max1),1,0)

delete the measure from the table visual, but add the measure in the viusal level filter of the table visual

select "show items when value is 1"

Best Regards

Maggie

[mnoah]

This 6 year old solution just saved my day!

[kurtazzopardi]

Thanks was looking for a similar solution. 

Still unsure however how MAX is returning the value for each "row" as opposed to the maximum value of all rows?

[Ashish_Mathur]

Hi,

What is PourDate?  Can the two date columns be interpreted as "Beginning Date" and "Ending Date"?  If yes, then how can the PourDate be before the ShipDate (For Order 5)?

[LivioLanzo]

One way you can do this is create a Calendar Filter table without relationships and then use a measure like this:

Number of Orders =
CALCULATE (
    COUNTROWS ( Orders ),
    FILTER (
        CALCULATETABLE (
            SUMMARIZE ( Orders, Orders[PourDate], Orders[ShipDate] ),
            ALL ( Orders[PourDate], Orders[ShipDate] )
        ),
        OR (
            Orders[ShipDate] IN VALUES ( 'Calendar Filter'[Date] ),
            Orders[PourDate] IN VALUES ( 'Calendar Filter'[Date] )
        )
    )
)