cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smileamile2
Helper II
Helper II

One table, 2 date slicers that work as an "or" not "and"

I have two date slicers, Create Date and Close Date. Both are used as relative date slicers and will select a date range. I have one table that just returns a bunch of item detail data, no aggregations. I need the slicers to filter the table as an "or" condition, rather than "and".

 

If I select Create Date in Last 1 week and Close Date in Next 1 week, I should see all records that either have a Create Date in the last week OR a Close Date in the next week.  Since the slicers can return multiple values, I think I need to use "ALLSELECTED" function, but I am having trouble figuring out how to use it in a measure to filter the table with since I am not calculating any aggregations. Anyone have ideas?

1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @smileamile2 

 

Create two another identical tables.

11.PNG

 

date 2 = ALLSELECTED('date'[date])

 

Apply two tables of date for two slicer respectively.

22.PNG

Then create a measure.

 

slicer_date = IF(SELECTEDVALUE('date'[date]) in VALUES('date 1'[date]) || SELECTEDVALUE('date'[date]) in VALUES('date 2'[date]),1,0)

 

44.PNG

 

Finally, put the measure on the data filter.

555.PNG

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards

Community Support Team _ Polly

View solution in original post

3 REPLIES 3
v-polly-msft
Community Support
Community Support

Hi @smileamile2 

 

Create two another identical tables.

11.PNG

 

date 2 = ALLSELECTED('date'[date])

 

Apply two tables of date for two slicer respectively.

22.PNG

Then create a measure.

 

slicer_date = IF(SELECTEDVALUE('date'[date]) in VALUES('date 1'[date]) || SELECTEDVALUE('date'[date]) in VALUES('date 2'[date]),1,0)

 

44.PNG

 

Finally, put the measure on the data filter.

555.PNG

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards

Community Support Team _ Polly

amitchandak
Super User
Super User

@smileamile2 , As long as all other columns other than the measure you created are un summarized, measure will filter the table 

 

With independent date table

example

measure =
VAR _max = MAXX(allselected('Date1'),'Date1' [Date])
return
countrows(filter(Table, Table[Create Date] >=_max-7 && [Create Date] <= _max && Table[close Date] >=_max && [Close Date] <= _max +7 ))

 

 

use this measure with all other fields as un summarized in visual.

 

Or filter this value is not blank in the visual level filter

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

@amitchandak 

Well, I can't have the +-7 in the measure itself. I have relative date slicers that allow user to select any date range that can change

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors