Join Two Columns to Single Attribute Table

[bvbull200]

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.

[barritown]

Hi @bvbull200,

Am I correct that you would like to assemble a matrix like the one below? 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[bvbull200]

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.

[barritown]

Hi again,

Yesterday I couldn't share the PBIX file with this solution, but today I can. Here it is - https://www.dropbox.com/scl/fi/agoxk4579pfvzwpwaomwt/community-peripherals.pbix?rlkey=1nh6wj3z4wysjn...

[barritown · Accepted solution]

@bvbull200,

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

My YouTube vlog in English

My YouTube vlog in Russian

[bvbull200]

I have finally made it back from some time away from this project and implemented this solution.  It works as advertised.  My tables have a lot more attributes than in the example, so there is a little more work I'm doing to get this far, but I wanted to thank you for the help in getting me this far.  I came to accept the solution, but looks like the admins beat me to it.  Very sorry for the delay in responding.

Cheers and thank you again. 

[barritown]

@bvbull200, 

I'm glad that you're advancing, but if I remember correctly your ideal solution would include showing attributes for the top level as well. Try to enhance your measures like shown below. 

And in plain text for convenience:

Price_ = 
VAR CurrentMainSKU = MIN ( [Main SKU] )
RETURN
IF ( ISINSCOPE ( 'Accessories Extended'[Accessory SKU] ),
     MIN ( Attributes[Price] ),
     CALCULATE ( MIN ( Attributes[Price] ),'Accessories Extended'[Accessory SKU] = CurrentMainSKU ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian