Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
This 6 year old solution just saved my day!
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?
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)?
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] ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |