Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
NewData
Frequent Visitor

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. 

Untitled.jpg

 

 

 

 

 

 

 

Thanks in advance for help

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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.

View solution in original post

NewData
Frequent Visitor

@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.

View solution in original post

7 REPLIES 7
NewData
Frequent Visitor

@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.

NewData
Frequent Visitor

@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
Not applicable

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
)

vpollymsft_0-1653960184838.png

 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
Super User
Super User

@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)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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 COLUMN

Anonymous
Not applicable

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.