Forum Discussion
Join Two Columns to Single Attribute Table
I have two tables. The first is a table that lists each sku number one time and has a number of attributes for each one. The second is a table that lists each combination of a main sku with its accessory sku. This is like listing a computer as the main sku number 5 times and in each row having a different peripheral sku number associated with it. The two tables are like this:
Attribute Table:
| SKU | Part # | Stocked? | Price | Brand |
| 710001 | Laptop | Y | $500 | HP |
| 740001 | Laptop | N | $400 | HP |
| 780003 | Laptop | Y | $600 | Dell |
| 790006 | Mouse | Y | $20 | HP |
| 830007 | Mouse | N | $50 | Dell |
| 760008 | Keyboard | Y | $75 | HP |
| 770007 | Keyboard | Y | $65 | Dell |
Accessories Table:
| Main SKU | Accessory SKU | Priority |
| 710001 | 790006 | 1 |
| 710001 | 760008 | 2 |
| 740001 | 790006 | 1 |
| 740001 | 760008 | 2 |
| 780003 | 830007 | 1 |
| 780003 | 770007 | 2 |
The attribute table is much wider and I may need to pull in a lot of attributes, but what I'm trying to do is have a way where I can use the attribute table to determin the part # and brand for each Main SKU and for each Accessory SKU. Can this be done via a relationship or am I having to merge queries at a point? The query merge is not appealing to me due to the size of the dataset (7MM+ rows on the accessory table and 4.8MM rows X 100 columns on the attribute table).
In my head, I end up with an matrix where the main sku is listed as the row with the accessory sku nested underneath it. If you expand the main sku, you'd see the part # for both the main sku and the accessory sku, along with any other attribute I included, but perhaps this isn't feasible.
Looking forward to any suggestions on this challenge.
Thank you for your help.
Try this non-ideal solution first with your big data. If it works, we will see if it can be idealized furhter. 🙂
The idea is:
1) You create an additional table with this code:
In plain text:
Accessories Extended = UNION ( Accessories, ADDCOLUMNS ( SUMMARIZE ( Accessories, [Main SKU] ), "Accessory SKU", [Main SKU], "Priority", 0 ) )2) Then you create a relationship:
3) Then - an hierarchy:
4) Then you create a set of measures like this one:
Part #_ = IF ( ISINSCOPE ( 'Accessories Extended'[Accessory SKU] ), MIN ( Attributes[Part #] ), BLANK () )5) Then you add a matrix visual an populate it with the hierarchy and the measures:
Best Regards,
Alexander
6 Replies
Hi bvbull200,
Am I correct that you would like to assemble a matrix like the one below?
Best Regards,
Alexander
This would be VERY good. Ideal would be to have the top level show the attributes without having to expand, but I could start with this for sure.
Try this non-ideal solution first with your big data. If it works, we will see if it can be idealized furhter. 🙂
The idea is:
1) You create an additional table with this code:
In plain text:
Accessories Extended = UNION ( Accessories, ADDCOLUMNS ( SUMMARIZE ( Accessories, [Main SKU] ), "Accessory SKU", [Main SKU], "Priority", 0 ) )2) Then you create a relationship:
3) Then - an hierarchy:
4) Then you create a set of measures like this one:
Part #_ = IF ( ISINSCOPE ( 'Accessories Extended'[Accessory SKU] ), MIN ( Attributes[Part #] ), BLANK () )5) Then you add a matrix visual an populate it with the hierarchy and the measures:
Best Regards,
Alexander
