Compare one row with multiple criteria with the others

[alavanka]

Hi all,

I need to compare the similarity of one row with the other rows, according multiple criteria (qualitative or quantitative) and with an adjusting weight.

I have an Excel file that reflects what I need to implement in powerBi but since I am recent in PBI I struggle to adapt the excel to it:

Item Comparing 

basically I select one row at the D4 cell and depending each characteristic / feature, I will get a score of similarity based on the weight I provided on cells E3 to J3.

Is this possible to implement in PowerBI? What is the best approach?

Many thanks!

[alavanka]

Hello,

If you look at the excel file, I selected the Product C and it shows the similarity with each of the others (where comparing with C = 1 or 100%):

I need to be able to select any product to compare with the others so I can have a similarity table like the 2nd one and also be able to change the weight of each Feature (changing the % at row 3) to see the changes / effect of each feature in my comparison.

Didn't understand your suggestions but I guess they are not be helpful in this case correct?

[v-yueyunzh-msft]

Hi , @alavanka 

Look on the excel you provide , i cannot get how to calculate the [Feature 1 calc] - [Feature 6 calc]？

I think the [score] = [Feature 1 calc] +[Feature 2 calc] + .. [Feature 6 calc]. Right?

Can you explain that how to calcualte the  [Feature 1 calc] - [Feature 6 calc] so that we can help you better.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[alavanka]

hello @v-yueyunzh-msft ,

first of all thank you for you help and sorry if I wasn't clear... anyway I realized it's better to transfer the file as xlsx to not mess up the formulas... 😞

The formula for K7 for example compares the value from E7 (A) with the correspondant of the selected product C (E9 = C). Since E7 (A) <> E9 (C) = 0 * 3% = 0

if I select another one for example K11 I am comparing the value of E11 (C) with the correspondent value of Feature 1 for product C (E9):

Since it's the same: E11 (C) = E9 (C) = 1 * 3% = 0,03

The score is the sum of all the [Feature X calc] and the idea is to be able to select any product in D4 and manipulate manually the weight of each feature E3:J3.

Is it possible to this in DAX? What do you suggest as approach?

Thanks in advance!

[v-yueyunzh-msft · Accepted solution]

Hi , @alavanka 

According to your description, it can be realized in Power BI Desktop.

Here are the steps you can refer to :

(1)We need create two table in Excel like this:

Product Table :

Calc Table:

(2)Then we need to click “New Table” to create a table in Power BI Desktop :(This table is used to put in the slicer)

Produnct Name = ALL('Product'[Product])

(3)We need to click “New Measure” to create seven measures like this:

Feature 1 calc = var _percentage =MAXX( FILTER( 'Calc' , 'Calc'[Feature]=1) , [Percentage])
var _feature1 = MAX('Product'[Feature 1])
var _current_product = SELECTEDVALUE( 'Product'[Product])
var _slicer = VALUES('Produnct Name'[Product])
var _same_feature1 =DISTINCT( SELECTCOLUMNS( FILTER(ALL('Product'), 'Product'[Product] in _slicer) ,"Feature 1-2" , [Feature 1]))
return
IF(_feature1 in _same_feature1 , _percentage , BLANK())

Feature 2 calc = var _percentage =MAXX( FILTER( 'Calc' , 'Calc'[Feature]=2) , [Percentage])
var _feature2 = MAX('Product'[Feature 2])
var _current_product = SELECTEDVALUE( 'Product'[Product])
var _slicer = VALUES('Produnct Name'[Product])
var _same_feature2 =DISTINCT( SELECTCOLUMNS( FILTER(ALL('Product'), 'Product'[Product] in _slicer) ,"Feature 2-2" , [Feature 2]))
return
IF(_feature2 in _same_feature2 , _percentage , BLANK())

Feature 3 calc = var _percentage =MAXX( FILTER( 'Calc' , 'Calc'[Feature]=3) , [Percentage])
var _feature3 = MAX('Product'[Feature 3])
var _current_product = SELECTEDVALUE( 'Product'[Product])
var _slicer = VALUES('Produnct Name'[Product])
var _same_feature3 =DISTINCT( SELECTCOLUMNS( FILTER(ALL('Product'), 'Product'[Product] in _slicer) ,"Feature 3-2" , [Feature 3]))
return
IF(_feature3 in _same_feature3 , _percentage , BLANK())

Feature 4 calc = var _percentage =MAXX( FILTER( 'Calc' , 'Calc'[Feature]=4) , [Percentage])
var _feature4 = MAX('Product'[Feature 4])
var _current_product = SELECTEDVALUE( 'Product'[Product])
var _slicer = VALUES('Produnct Name'[Product])
var _same_feature4 =DISTINCT( SELECTCOLUMNS( FILTER(ALL('Product'), 'Product'[Product] in _slicer) ,"Feature 4-2" , [Feature 4]))
return
IF(_feature4 in _same_feature4 , _percentage , BLANK())

Feature 5 calc = var _percentage =MAXX( FILTER( 'Calc' , 'Calc'[Feature]=5) , [Percentage])
var _feature5 = MAX('Product'[Feature 5])
var _current_product = SELECTEDVALUE( 'Product'[Product])
var _slicer = VALUES('Produnct Name'[Product])
var _same_feature5 =DISTINCT( SELECTCOLUMNS( FILTER(ALL('Product'), 'Product'[Product] in _slicer) ,"Feature 5-2" , [Feature 5]))
return
IF(_feature5 in _same_feature5 , _percentage , BLANK())

Feature 6 calc = var _percentage =MAXX( FILTER( 'Calc' , 'Calc'[Feature]=6) , [Percentage])
var _feature6 = MAX('Product'[Feature 6])
var _current_product = SELECTEDVALUE( 'Product'[Product])
var _slicer = VALUES('Produnct Name'[Product])
var _same_feature6 =DISTINCT( SELECTCOLUMNS( FILTER(ALL('Product'), 'Product'[Product] in _slicer) ,"Feature 6-2" , [Feature 6]))
return
IF(_feature6 in _same_feature6 , _percentage , BLANK())

score = [Feature 1 calc]+ [Feature 2 calc]+ [Feature 3 calc] + [Feature 4 calc] + [Feature 5 calc] + [Feature 6 calc]

(4)Then we can put the ‘Product Name’[Product] in the slicer and the other fields we need in the visual , then we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[alavanka]

You are the best @v-yueyunzh-msft !! It's exactly what I am struggling... you just gave me the entire solution! Many, many thanks!!! 🙌🙌🙌

[alavanka]

Hello,

Just one more question...

If I want to change the values in CALC table, is there a way to do it dynamically (without going to Query Editor and change the value manually)?

I would like to work in different scenarios where in each analysis I would give different weights to each feature (by changing the values in CALC table) but guaranteing that the total is 100% or at least it doesn't exceed 100% or 1.

I am searching for What-if parameters and numeric range slicers but so far I didn't find any working solution.

I opened another post with this topic but I start to realize that both are related therefore I pulled the topic to this thread:

Re: Update table using slicers - Microsoft Power BI Community

Any help would great!

Thanks again for your valuable help!

[amitchandak]

@alavanka , I think Earlier can help

DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

If needed, first add an index column in the power query

Power Query- Index Column: https://youtu.be/NS4esnCDqVw

[alavanka]

Are you sure I could use EARLIER function to my case?

I need to rank my rows by selecting a certain row and if same column match between them give them one value. If doesn't match it's 0.