Forum Discussion

rhubarb's avatar
rhubarb
New Member
3 years ago

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
1A111
2A211
3A312
4B121
5C222
6C323

 

The Product table looks like this:

Product IDProduct NameAttribute 1Attribute 2Attribute 3
1Name1S123TRUE
2Name2M456FALSE
3Name3L789TRUE
11Name11XS234FALSE
12Name12M345TRUE
13Name13L456FALSE
21Name21S678TRUE
22Name22L456FALSE
23Name23XL890TRUE

 

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!

 

3 Replies

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

    • rhubarb's avatar
      rhubarb
      New Member

      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.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        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?