Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am trying to create the measure which check the whether "Range start" and "Range end" is blank then output should be 1 or "Range start" and "Range end" is between date range of slicer then 0 else 1
below is the table
all dates are in mm/dd/yyyy format
if date slicer range is 11/25/2021 to 11/30/2021 Then expected output should be
Solved! Go to Solution.
Hi, @Anonymous
Try to create a measure like this:
Measure =
var _minDate=CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
var _maxDate=CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))
var _rangeStart=CALCULATE(MAX('Table'[Range start]),FILTER(ALL('Table'),'Table'[Employeeid]=MAX('Table'[Employeeid])))
var _rangeEnd=CALCULATE(MAX('Table'[Range end]),FILTER(ALL('Table'),'Table'[Employeeid]=MAX('Table'[Employeeid])))
var _slicer=GENERATESERIES(_minDate,_maxDate)
var _t=GENERATESERIES(IF(_rangeStart<>BLANK(),_rangeStart,0),IF(_rangeEnd<>BLANK(),_rangeEnd,0))
var _count=COUNTROWS(_slicer)
var _except=COUNTROWS(EXCEPT(_slicer,_t))
return IF(_count<>_except,0,1)
Result:
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Try to create a measure like this:
Measure =
var _minDate=CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
var _maxDate=CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))
var _rangeStart=CALCULATE(MAX('Table'[Range start]),FILTER(ALL('Table'),'Table'[Employeeid]=MAX('Table'[Employeeid])))
var _rangeEnd=CALCULATE(MAX('Table'[Range end]),FILTER(ALL('Table'),'Table'[Employeeid]=MAX('Table'[Employeeid])))
var _slicer=GENERATESERIES(_minDate,_maxDate)
var _t=GENERATESERIES(IF(_rangeStart<>BLANK(),_rangeStart,0),IF(_rangeEnd<>BLANK(),_rangeEnd,0))
var _count=COUNTROWS(_slicer)
var _except=COUNTROWS(EXCEPT(_slicer,_t))
return IF(_count<>_except,0,1)
Result:
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous can you try this measure
Measure = if(max(tbl[Range Start])>=min('Date'[Date])&&min(tbl[Range End])<=max('Date'[Date]),1,0)