cancel
Showing results for
Did you mean:

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.

Frequent Visitor

## Finding nearest value from table with respect to selected value in slicer

Hi,
I have Created a Slicer for Entering a value by creating a calculated table :

Quantity Table = GENERATESERIES(0,90000,1)
I have a fact table - Rate Table
 Seq Qty Price 1 100 1000 2 250 1500 3 500 2000 4 1000 3000
Question is
How to find the nearest value of Qty and Price from the table with respect to the selected value in slicer ?

For eg : Entered Value in slicer = 1276
The Output : Qty 1000 and Price 3000
2 REPLIES 2
Community Support

The approach @amitchandak  offers is fantastic,  In addition to referring to the solutions @amitchandak provides, you can also consider the following methods.

You can refer to the following measure

``````Price = VAR A=SELECTEDVALUE(Quantity[Value])
var c=SUMMARIZE(b,[Seq ],'Rate'[Qty],'Rate'[Price],[Divide],"Min",MINX(b,[Divide]))
return MINX(FILTER(c,[Min]=[Divide]),[Price])``````
``````QTY = VAR A=SELECTEDVALUE(Quantity[Value])
var c=SUMMARIZE(b,[Seq ],'Rate'[Qty],'Rate'[Price],[Divide],"Min",MINX(b,[Divide]))
return MINX(FILTER(c,[Min]=[Divide]),[Qty])``````

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@PowerBi_Xandar , create measures like

Qty M =
var _max = mInx(filter(Table, [Qty]>= selectedvalue('Quantity Table'[value])), Table[Qty])
var _min = Maxx(filter(Table, [Qty]<= selectedvalue('Quantity Table'[value])), Table[Qty])
return
calculate(Sum(Table[Qty]), filter(Table, Table[Qty] =if(_max -[Qty]> [Qty] -_min , _min, _max)))

Price M =
var _max = mInx(filter(Table, [Qty]>= selectedvalue('Quantity Table'[value])), Table[Qty])
var _min = Maxx(filter(Table, [Qty]<= selectedvalue('Quantity Table'[value])), Table[Qty])
return
calculate(Sum(Table[Price]), filter(Table, Table[Qty] =if(_max -[Qty]> [Qty] -_min , _min, _max)))

or use one of them a visual level filter

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors