Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shashanka_88
Regular Visitor

Similar products based on Price

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

A1000
B4000
C1000
D2000
E4000
F1000
G11000

 

@PowerBI 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1717334912327.png

 

 

Jihwan_Kim_0-1717334893764.png

 

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 )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1717334912327.png

 

 

Jihwan_Kim_0-1717334893764.png

 

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 )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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