The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Problem:
Create a Date Range Slicer that filters Job Sites based on, StartDate and EndDate range.
IF the Date Range Slicer falls within the "StartDate/EndDate" range then we show the Job Site data.
See the "Show?" Column to view 5 examples below.
Fields:
Date Filter Range = An example of the "Date Range Slicer" selection.
Start Date and End Date = The sub-set of date ranges that the slicer needs to evaluate against.
Show? = This is just an example of how the Job Site data should be filtered by the "Date Filter Range" slicer.
Example Data:
Date Filter Range | Start Date | End Date | Show? | ||
1/1/2021 | Job Site 1 | 12/25/2020 | 1/9/2021 | yes | |
1/2/2021 | Job Site 2 | 1/1/2021 | 2/1/2021 | yes | |
1/3/2021 | Job Site 3 | 12/22/2020 | 12/29/2020 | no | |
1/4/2021 | Job Site 4 | 1/21/2020 | 2/1/2021 | yes | |
1/5/2021 | Job Site 5 | 12/31/2020 | 2/1/2021 | yes | |
1/6/2021 | |||||
1/7/2021 | |||||
1/8/2021 | |||||
1/9/2021 | |||||
1/10/2021 | |||||
1/11/2021 | |||||
1/12/2021 | |||||
1/13/2021 | |||||
1/14/2021 | |||||
1/15/2021 | |||||
1/16/2021 | |||||
1/17/2021 | |||||
1/18/2021 | |||||
1/19/2021 | |||||
1/20/2021 | |||||
1/21/2021 | |||||
1/22/2021 | |||||
1/23/2021 | |||||
1/24/2021 | |||||
1/25/2021 | |||||
1/26/2021 | |||||
1/27/2021 | |||||
1/28/2021 | |||||
1/29/2021 | |||||
1/30/2021 | |||||
1/31/2021 |
Solved! Go to Solution.
Hi @Anonymous ,
Create a measure as below:
Show? =
var _mindate=MINX(ALLSELECTED('Table'),'Table'[Date Filter Range])
var _maxdate=MAXX(ALLSELECTED('Table'),'Table'[Date Filter Range])
Return
IF((MAX('Table (2)'[End Date]) in FILTERS('Table'[Date Filter Range])&&MAX('Table (2)'[End Date])>=_mindate)||(MAX('Table (2)'[End Date])>=_maxdate&&MAX('Table (2)'[Start Date])<=_maxdate),"yes","no")
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
Create a measure as below:
Show? =
var _mindate=MINX(ALLSELECTED('Table'),'Table'[Date Filter Range])
var _maxdate=MAXX(ALLSELECTED('Table'),'Table'[Date Filter Range])
Return
IF((MAX('Table (2)'[End Date]) in FILTERS('Table'[Date Filter Range])&&MAX('Table (2)'[End Date])>=_mindate)||(MAX('Table (2)'[End Date])>=_maxdate&&MAX('Table (2)'[Start Date])<=_maxdate),"yes","no")
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Anonymous I would think that something like this would work:
Measure =
VAR __MaxDate = MAX('DateTable'[Date])
VAR __MinDate = MIN('DateTable'[Date])
VAR __StartDate = MAX('Table'[StartDate])
VAR __EndDate = MAX('Table'[EndDate])
RETURN
IF( (__MinDate >= __StartDate && __MinDate <= __EndDate) || (__MaxDate <= __EndDate && __MaxDate >= __StartDate),1,0)
Hi, @Anonymous
I am not sure if I understood your question correctly. but please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
@Anonymous You want a Complex Selector. An example is here. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
Basically a measure that returns 1 or 0 and you filter on it. Specifics vary but I would imagine in your case you would get the MIN and MAX of the date column in your date range slicer and then use a SWITCH or IF statement to determine if it is in range. I wasn't clear on whether just part of it had to be in range or all of it.
Greg,
Thank you for the Reply.
Just part of the StartDate/EndDate range needs to be within the "Date Range Slicer" selection to include the job site.