Select a value from a table depending on a range

[Syndicate_Admin]

Good morning Community,

I have a mileage range table that contains the following structure:

FROM TO RANGE

10.000 20.000 20.000

20.000 30.000 30.000

30.000 40.000 40.000

and, depending on the value of a measurement, I evaluate it in the table and I must take the value from the RANK column, since they want to round the kilometers of 10,000 in 10,000. Here I accompany the formula:

Mileage Range =

WHERE __KmTotales= [Total Km. Proposed]+[Total Km. Real]

RETURN

IF(

VALUES('MILEAGE RANGES'[From])>=__KmTotales &&

VALUES('MILEAGE RANGES'[Until])<__KmTotales,

VALUES('MILEAGE RANGES'[Range])

)

The problem is that I use the VALUES function for this and it always returns the message "A table of several values was provided, where a single value was expected" is expected. I can't get him back the proper range.

How could I fix it?

Thank you all very much.

[amitchandak]

@Syndicate_Admin , The information you have provided is not making the problem clear to me. Can you please explain with an example?

If you want a bucket to measure 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing, or Binning: https://youtu.be/CuczXPj0N-k

or

https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...

Appreciate your Kudos.

[Syndicate_Admin · Accepted solution]

Hello @Syndicate_Admin ,

Finally, I tried the article you shared with me(https://www.daxpatterns.com/static-segmentation/), adapted it and it worked for me. I share my measurement:

Mileage Range =

WHERE __KmTotales= [Total Km. Proposed]+[Total Km. Real]

WHERE __KmPropuestos=

FILTER (

'MILEAGE RANGES',

AND (

'MILEAGE RANGES'[From] < __KmTotales,

'MILEAGE RANGES'[Until] >= __KmTotales

)

)

WHERE Result =

CALCULATE (

DISTINCT ( 'MILEAGE RANGES'[Range] ),

__KmPropuestos

)

RETURN

Result

Thank you very much for your solution!! You helped me get the calculation I wanted!

Greetings and thanks again to the community!

[Syndicate_Admin]

Hello @Syndicate_Admin ,

Thank you for your reply.

For example, if the calculation of the kilometers consumed is 23,678 km, you would look at the Range table and since it is between 20,000 km and 130,000 km, you should be able to take from the Range table the rank column, which, in this case, would be 30,000 km. That's the value I'm looking for. It is about rounding the kilometers in strips of 10,000 km. If the user makes x kilometers, I compare them with the Range table and select the value in the Range column to the strip to which it belongs.

If you need more clarification, please let me know.

Thank you.