count distinct of 2 modalities at scale

Question

My data looks like that, a normal star schema:

id1	id2	Value
p1	q1	1
p2	q2	1
p3	q3	1
p1	q2	1
p2	q3	0
p3	q1	1
p1	q3	0
p2	q1	1
p3	q2	0

I am looking for a cross tab that would look like this, with id2 on top and id2 on the side, and each cell the distinct count of the number of id1.

id1	q1	q2	q3
q1	count distinct( id1, where value == 1 and id2 == q1)	count distinct( id1, where filter value == 1 and ( id2 == q1 or id2 == q2))	count distinct( id1, where filter value == 1 and ( id2 == q1 or id2 == q3))
q2	count distinct( id1, where value == 1 and ( id2 == q2 or id2 == q2))	count distinct( id1, where filter value == 1 and id2 == q2)	count distinct( id1, where filter value == 1 and ( id2 == q2 or id2 == q3))
q2	count distinct( id1, where value == 1 and ( id3 == q2 or id2 == q1))	count distinct( id1, where filter value == 1 and ( id2 == q3 or id2 == q2))	count distinct( id1, where filter value == 1 and id2 == q3)

I can get each cell manually without too much issues: filter on the values of id2. But I am looking for a way to have it for all the cells at once. Any idea?

Answer 1

You can create two calculated tables, where Data is your original table:

Axis1 = VALUES(Data[id2])
Axis2 = VALUES(Data[id2])

No active relation should be establised between them and Data. Then create measure:

Measure = CALCULATE(
    DISTINCTCOUNT(Data[id1]), 
    'Data'[Value] = 1, 
    'Data'[id2] IN {SELECTEDVALUE(Axis2[id2]),SELECTEDVALUE(Axis1[id2])}
)

Choose Matrix visual and add data like this:

You'll get:

For measure calculated this way:

Measure2 = CALCULATE(
    COUNT(Data[id1]), 
    'Data'[Value] = 1, 
    'Data'[id2] IN {SELECTEDVALUE(Axis2[id2]),SELECTEDVALUE(Axis1[id2])}
)

The result would be like:

To compare: