Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ID | Category | Old Product ID | New Product ID |
1 | A | 1 | 11 |
2 | A | 2 | 11 |
3 | A | 3 | 12 |
4 | B | 1 | 21 |
5 | C | 2 | 22 |
6 | C | 3 | 23 |
The Product table looks like this:
Product ID | Product Name | Attribute 1 | Attribute 2 | Attribute 3 |
1 | Name1 | S | 123 | TRUE |
2 | Name2 | M | 456 | FALSE |
3 | Name3 | L | 789 | TRUE |
11 | Name11 | XS | 234 | FALSE |
12 | Name12 | M | 345 | TRUE |
13 | Name13 | L | 456 | FALSE |
21 | Name21 | S | 678 | TRUE |
22 | Name22 | L | 456 | FALSE |
23 | Name23 | XL | 890 | TRUE |
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:
So the report looks something like below, except that the count of differences is not correct.
I have 4 measures:
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!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |