Forum Discussion
Optimization of getting intersection between values in same table
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.
2 Replies
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?
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 🙂
