cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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 Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors