Heatmap showing intersection where the same dimension is both column and row

[ALH]

I’m attempting to create a heatmap and am running into difficulties.
I have a dataset where a single unique id can own multiple products.

I would like to create a heatmap where I show counts by unique id that owns multiple products. So for this, the ‘Products’ in the heatmap would be both the columns and the rows, and the intersection is where the count is reported. For example:

For example, Unique ID = 1 has products A, B and C. This would count ‘1’ for the A-B combination, ‘1’ for the B-C combination and ‘1’ for the A-C combination.
For Unique ID = 6, it does not show up on the heatmap because that unique id is not associated with multiple products, just the one (‘C’).

For this, I have tried a nested distinctcountover within a distinctcountover, but no success. Any thoughts on how this could be reported? Thanks.

[Jesse]

Hi @ALH - this is typically called a Market Basket and one way to do it is to join the table to itself. I replicated your sample table in excel and created a dataset in QS for it. In the data prep window you need to Add Data and upload the file again (considered two different sources when using flat files), and then join them on ID+ID.

Then add a calculated field to remove the rows where the product is the same:

Then build your pivot table and make sure you use Distinct Count of ID on the Values.

You could filter out the 0’s if you want, or add conditional formatting to turn the text and cell background color to be the same color when it equals 0.

Hope this helps!

[ALH]

Thanks Jesse. This is a terrific answer and appears to be the exact solution I needed. Thank you for your time and for the excellent explanation!!!