Select Product and show similar products based on multiple characteristics

[bizintelguy604]

Hi All,

I have a dashboard I want to create where a user selects (from a drop down slicer) a Product and then a table next to the slicer returns "similar" products.

Example: 

User selects Product A (which is green and size 2). Table next to the slicer returns all Products that are green and also size 2 and 3 (because users may want a size larger as well). 

Is this possible? How would I go about setting this up? Create relationships on 2 tables based on 2 columns (which I don't believe is possible in Power BI)?

[v-lili6-msft · Accepted solution]

hi @bizintelguy604 

You could try this way as below:

Step1:

You need a separate dim product table for the slicer, you could try this formula to create a new dim product table.

Dim Product = VALUES('Table'[Product])

Step2:

Create a measure as below:

to filter = 
var _colors=CALCULATETABLE(VALUES('Table'[Color]),FILTER(ALLSELECTED('Table'),'Table'[Product]=SELECTEDVALUE('Dim Product'[Product]))) return
CALCULATE(MAX('Table'[Product]),FILTER('Table', 'Table'[Color] in _colors))

Step3:

Then drag it to the table visual and set visual level filter is not blank

and here is a simple sample pbix file, please try it.

Regards,

Lin

[Anonymous]

This solution is so helpful, brilliant indeed!!

[amitchandak]

assume product table has products

name, size, color

Measure =
var _size = minx(allselected(product),product[size]) // You can also use product in place allselected(product) if needed
var _color = minx(allselected(product),product[color])
return
calculate(count(product), all(product),product[size]=_size,product[color]=_color)

table =
var _size = minx(allselected(product),product[size]) // You can also use product in place allselected(product) if needed
var _color = minx(allselected(product),product[color])
return
summarize(filter( all(product),product[size]=_size,product[color]=_color), product[name])

In case you create a measure, use it with the product name in a table or matrix.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin