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,
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, @Surya9
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, @Surya9
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.
@Surya9 can you try this measure
Measure = if(max(tbl[Range Start])>=min('Date'[Date])&&min(tbl[Range End])<=max('Date'[Date]),1,0)
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 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |