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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GilesWalker
Skilled Sharer
Skilled Sharer

Has One Value but only between two set numbers

Hi everyone,

 

I have a table of data (called sheet1) which contains a location defined as a number between 0 and 700.00 and goes up in 0.1 increments. 

 

I then have another table which has these same locations but for only a smaller amount, example - from 10.2 through to 100.9. This comes from a machine that does readings.

 

There are a number of other columns I use to create my formula which is called "Average TQI". This provides the average TQI for each 0.1 location. However the issue I have is sometimes the machine doing the measurements will miss a couple 0.1 increments, for example it will have 30.1, 30.2, and then misses 30.3, and goes to 30.4. I have used the HASONEVALUE formula as follows to give me a zero where there is a missed increment:

 

Average TQI = IF(HASONEVALUE(Sheet1[Custom location]),DIVIDE([TQI sum],[TQI count rows]),0)

 

This works, however it gives me a 0 value for the entire range from 0 to 700 becasue I am using the sheet1 table which has all values.

 

My question to you is can I get the above formula to work to only show from the start of the measurement to the end of the measurement? I was think if I use max and min but cant seem to figure out how to get the formula to work.

 

Here is a snapshot of the data:

 

TQI1.PNG

 

The column named track section contains the location, there are anywhere between 3 and 6 different track sections which will all have their own min and max location start and end points.

 

Thanks,

 

Giles

 

1 REPLY 1
v-haibl-msft
Employee
Employee

@GilesWalker

 

I’m a little confused about your requirement. The snapshot you provided should be sheet1 table. Are TQI sum and TQI count rows measures? Which table do you want Average TQI to show in, sheet1 or sheet2? You said that you want get the formula to work to only show from the start of the measurement to the end of the measurement, could you please give a simple sample along with two sheets tables?

 

Best Regards,

Herbert

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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