Forum Discussion

PowerBi_Xandar's avatar
PowerBi_Xandar
Frequent Visitor
3 years ago

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...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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