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.
My original dataset looks like this:
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 1 is member of work group A only. Company 3 has membership to work groups A, B and C.
My expected output would be:
| A | B | C | D |
A | 3 | 0 | 1 | 1 |
B | 0 | 2 | 2 | 0 |
C | 1 | 2 | 2 | 0 |
D | 1 | 0 | 0 | 1 |
… | | | | |
The diagonal shows the total membership in a given work group, all other field show the “overlap” as in how many companies are in 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.
My approach (that doesn’t work 😞 ) was to create three tables:
table1: source
(this is the unpivoted original dataset with an extra copy of the Workgroup column)
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 |
… | | | |
table 2: Workgrouplist1
table 3: Workgrouplist2
There are no relationships between these three tables.
And here is the DAX code of my measure:
countifs =
VAR CurrentX = SELECTEDVALUE('Workgrouplist2'[Workgroups])
VAR CurrentY = SELECTEDVALUE('Workgrouplist1'[Workgroups])
VAR Cooccurrence =
CALCULATE (
COUNTROWS('source'),
FILTER(
'source',
'source'[Workgroup] = CurrentX
&&
'source'[Workgroup copy] = CurrentY
&& 'source'[Value] = 1
)
)
RETURN
Cooccurrence
The actual output is
What is my mistake?
