Showing results for 
Search instead for 
Did you mean: 
New Member

Difficulty counting differences between old and new products

Hi, for the sake of this question I have a set of old products which are being migrated to new products. Each product has a set of attributes which I want to compare so that I know how many differences there are for each migration mapping.


Here is some data. The mapping table looks like this:


Mapping IDCategoryOld Product IDNew Product ID


The Product table looks like this:

Product IDProduct NameAttribute 1Attribute 2Attribute 3


I want to know the number of differences across all Attribute columns for the chosen 'Old' and 'New' products. Also, the number of Attribute columns may change.


So far I have separated the attribute fields into a separate table in Power BI so that the model looks like:


In my report I want to select a mapping from product X to product Y and show:

  • A list of attributes and their values for both products
  • Whether the attribute is the same for both products
  • A count of how many differences there are between products

So the report looks something like below, except that the count of differences is not correct.


I have 4 measures:

  1. Old Product Attribute Value = SELECTEDVALUE('Product Attributes'[Value])
  2. New Product Attribute Value = CALCULATE(SELECTEDVALUE('Product Attributes'[Value]),USERELATIONSHIP(Mappings[New Product ID],Products[Product ID]))
  3. Attribute Match = IF([New Product Attribute Value] = [Old Product Attribute Value], TRUE())
  4. Count Differences = COUNTROWS(FILTER('Product Attributes',[Attribute Match] <> TRUE()))

Firstly, is it the best approach to seperate the attributes into another table or is there a better way to count differences across columns without unpivoting the product table?

Secondly, if splitting is a resonable approach then why is the 'Count Differences' measure not producing the value I expect to see?


Thanks in advance!


Super User
Super User

Your product table is missing the category field.  Without that your mapping will be ambiguous.

Thanks for looking @lbendlin. The category isn't related to the product though, it's only really there to show that the product mapping isn't always 1 to 1. For example, a customer in Category A with Product 1 is mapped to Product 11 but a customer in Category B with the same product 1 is mapped to Product 21.


In my example I've filtered the page to display for a single row in the mapping table, so in that case should only be one value for each of the relationships of Old Product ID > Product ID and New Product ID > Product.

For example, a customer in Category A with Product 1 is mapped to Product 11 but a customer in Category B with the same product 1 is mapped to Product 21.


I consider that to be ambiguous.  I have not yet understood how such a situation should be handled. Random assignments?

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors