Co-occurrence matrix

Question

Hi,I have a dataset of several thousand companies that have memberships in work groups. There are 16 different work groups (A-P). Companies can be member of one or several groups. Most are members in one or two groups. I am trying to figure out the most common combinations of work groups. I think this would be called a co-occurrence matrix.&nbsp;My original dataset looks like this:Work groupsABCDCompany 11000Company 20110Company 31110Company 41001…&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Company 1 is member of work group A only. Company 3 has membership to work groups A, B and C.&nbsp;My expected output would be:&nbsp;ABCDA3011B0220C1220D1001…&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The diagonal shows the total membership in a given work group, all other field show the “overlap” as in how many companies are in&nbsp; both those work groups. It doesn’t matter to me whether the output is in a full matrix (where all combinations appear twice – once above and once below the diagonal) or a triangle shape.&nbsp;My approach (that doesn’t work 😞 ) was to create three tables:&nbsp;table1: source(this is the unpivoted original dataset with an extra copy of the Workgroup column)&nbsp;CompanyWorkgroupWorkgroup copyValueCompany1AA1Company1BB0Company1CC0Company1DD0Company2AA0Company2BB1Company2CC1Company2DD0…&nbsp;&nbsp;&nbsp;&nbsp;table 2: Workgrouplist1WorkgroupsABCD…&nbsp;table 3: Workgrouplist2Workgroups&nbsp;ABCD…&nbsp;There are no relationships between these three tables.&nbsp;And here is the DAX code of my measure:countifs =VAR CurrentX = SELECTEDVALUE('Workgrouplist2'[Workgroups])VAR CurrentY = SELECTEDVALUE('Workgrouplist1'[Workgroups])VAR Cooccurrence =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('source'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'source',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'source'[Workgroup] = CurrentX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'source'[Workgroup copy] = CurrentY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; 'source'[Value] = 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )RETURN&nbsp; &nbsp; Cooccurrence&nbsp;&nbsp;The actual output is&nbsp;ABCDA3&nbsp;&nbsp;&nbsp;B&nbsp;2&nbsp;&nbsp;C&nbsp;&nbsp;2&nbsp;D&nbsp;&nbsp;&nbsp;1&nbsp;What is my mistake?

suus · Answer

Hi&nbsp;Anonymous,Thank you for asking. The numbers represent the number of companies that participate in the respective two workgroups. No company is a member of both A and B, one company is in both A and C, two are in both B and C etc. The two red triangles show in fact the same numbers. So a way to display only one of these triangles would be perfect (look better than two that replicate the same information).&nbsp;Thank you!! Best, Suus

anonymous · Answer

Hi,&nbsp;Suus&nbsp;Didn't get the logic of the implementation of the numbers in the red box, can you explain it in more detail
Best Regards,YangCommunity Support Team
&nbsp;

Forum Discussion

Co-occurrence matrix

2 Replies

Related content

DAX Measure - Co-occurrences

Co-Occurrence of Companies (Displayed in a matrix?)

Co-occurrence Matrix

Matrix table

sorting matrix

Work groups	A	B	C	D
Company 1	1	0	0	0
Company 2	0	1	1	0
Company 3	1	1	1	0
Company 4	1	0	0	1
…

Company	Workgroup	Workgroup copy	Value
Company1	A	A	1
Company1	B	B	0
Company1	C	C	0
Company1	D	D	0
Company2	A	A	0
Company2	B	B	1
Company2	C	C	1
Company2	D	D	0
…