Optimization of getting intersection between values in same table

[ide]

Hi guys,

I'm stuck with optimization my dashboard. Let's say that i have this piece of data:

id	values
1	a
1	b
1	b
2	a
2	b
2	c
2	c
3	a
3	b
3	c
3	d
3	e
4	b
4	c
4	c
4	c
4	e
4	e
4	d

What i want to achive is intersection between values in column "values" and it should be % of distinct count. The accurate and readable visualisation would be a matrix, so i'll get the following:

id	1	2	3	4
1	100%	67%	40%	25%
2	67%	100%	60%	50%
3	40%	60%	100%	80%
4	25%	50%	80%	100%

I already solve this task by creating new table with same data, make relationship between both, make matrix with rows from table1[id], column from table2[id] and distinct count %of row total (not measure) from table2[values]. 

This working fine with small dataset, but now i want to put a 800mb dataset, so of course it's a large amount of data and result can only be displayed if i filtered out few values from both tables [id]. Probably there is more accurate way to get the same. 

Thanks.

[lbendlin]

Apart from deduplication and calculating only the "top" triangle there is not much you can do. It will be a cartesian product, and with large numbers of values that cartesian will be huge.

What are you planning to do with the result?

[ide]

Business want to see intersection choosing differents IDs to make any decisions...

I got about deduplication, seems it can reduce amount of rows. But this dataset is only for 1 month period. I assume they will ask me to do the same with 3 month period 🙂