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 QtyPrice
11001000
22501500
35002000
410003000
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

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi PowerBi_Xandar 

    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 b=ADDCOLUMNS(ALLSELECTED('Rate'),"Divide",IF(a>[Qty],DIVIDE(a,[Qty]),DIVIDE([Qty],a)))
    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 b=ADDCOLUMNS(ALLSELECTED('Rate'),"Divide",IF(a>[Qty],DIVIDE(a,[Qty]),DIVIDE([Qty],a)))
    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.