Forum Discussion
Return first value in given hour range over multiple dates
Hello,
I am trying to only return a "1" when a particular model reaches certain set of ranges.
Currently, my data keeps returning duplicates. Returns 1 each time that unit is in that range.
Example
Important ranges are from 10-15 and 20-25. Each time that unit hits return "1" the first time based on dates. Below table shows Quarters (Q1-Q3), Model, Value equals Hour range. Last column is desired results.
Thanks in advance for help
Hi NewData ,
If you want to chenge the measure to a column, please have a try.
column= var _min=minx(filter(allselected(table),table[model=earlier(table[model])&&((value>=10||value<=15)&&value>=20||value<=25))),table[date]) return if(table[date]=_min,1,0)If I have misunderstood your meaning, please provide the output you want in the form of a picture.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous
I was able to modify your code to get desired solution
column=
var _min1=minx(filter(allselected(table),table[model=earlier(table[model])&&((value>=10&&value<=15))),table[date])
var _min2=minx(filter(allselected(table),table[model=earlier(table[model])&&((value>=20&&value<=25))),table[date])
return
if(table[date]=_min1,1,If(table[date]=_min2,1,
0))
Thank you for assistance.
7 Replies
NewData , Try a meausre like
new meausre =
var _min = minx(filter(allselected(Table), Table[Model] = max(Table[Model]) && (([Value] >=10 && [Value] <=15) || ([Value] >=20 && [Value] <=25))), Table[Date])
return
if( Max(Table[Date]) = _min, 1, 0)amitchandak , I was able to get your code to return values for each instance by adding more variables for each measure.
i would like modify this measure to be a column in the table. Any advice on how to make this measure work for NEW COLUMNHi NewData ,
If you want to chenge the measure to a column, please have a try.
column= var _min=minx(filter(allselected(table),table[model=earlier(table[model])&&((value>=10||value<=15)&&value>=20||value<=25))),table[date]) return if(table[date]=_min,1,0)If I have misunderstood your meaning, please provide the output you want in the form of a picture.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi NewData ,
Please refer to my pbix file to see if it helps you.
Create a measure.
meausre(first value not in range) = IF ( ( MAX ( 'Table'[value] ) >= 10 && MAX ( 'Table'[value] ) < 15 ) || ( MAX ( 'Table'[value] ) >= 20 && MAX ( 'Table'[value] ) < 25 ), 1, 0 )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous ,
When applying to entire dataset, it would return a 1 on same Model for each date it's in the range. I would like it to only show a "1" when it's the first instance in the range.
amitchandak , thank you for the assistance. I used your code and it return "1" for only first instance, but not second range on another date.
Anonymous
I was able to modify your code to get desired solution
column=
var _min1=minx(filter(allselected(table),table[model=earlier(table[model])&&((value>=10&&value<=15))),table[date])
var _min2=minx(filter(allselected(table),table[model=earlier(table[model])&&((value>=20&&value<=25))),table[date])
return
if(table[date]=_min1,1,If(table[date]=_min2,1,
0))
Thank you for assistance.
