Similar products based on Price

[shashanka_88]

I have a table, Orders with ProductName and UnitPrice. I want to create a power BI dashboard where the user chooses the ProductName from a slicer and be able to see 3 products with UnitPrices closest to the selcted product.

ProductName UnitPrice

A	1000
B	4000
C	1000
D	2000
E	4000
F	1000
G	11000

@PowerBI 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the  below picture and the attached pbix file.

RANK function (DAX) - DAX | Microsoft Learn

expected result: = 
VAR _selectedproduct =
    MAX ( product_slicer[product_name] )
VAR _selectedprice =
    MAXX (
        FILTER ( ALL ( 'product' ), 'product'[product_name] = _selectedproduct ),
        'product'[unit_price]
    )
VAR _t =
    FILTER ( ALL ( 'product' ), 'product'[product_name] <> _selectedproduct )
VAR _pricediff =
    ADDCOLUMNS (
        _t,
        "@diff", ABS ( CALCULATE ( SUM ( 'product'[unit_price] ) ) - _selectedprice )
    )
VAR _diffrank =
    ADDCOLUMNS (
        _pricediff,
        "@diffrank", RANK ( SKIP, _pricediff, ORDERBY ( [@diff], ASC ) )
    )
VAR _condition =
    SUMMARIZE ( FILTER ( _diffrank, [@diffrank] <= 3 ), 'product'[product_name] )
RETURN
    CALCULATE (
        SUM ( 'product'[unit_price] ),
        KEEPFILTERS ( 'product'[product_name] IN _condition )
    )