Compare two fields by Coexistence and Exclusivity - DAX Approach

[dantesedmond]

Hello,

I have two main fields:

- Relative

- Child

And I have two separate slicers:

- Slicer 1: Select Children A

- Slicer 2: Select Children B

I'm looking for 3 DAX expressions to return:

- The count of family members shared by the two selected Children

- The count of family members exclusive to the selected Children A

- The count of family members exclusive to the selected Children B

Requirements

- The 3 expressions need to be measures for them to be dynamic within other slicer's contexts, such as age, occupation, etc.

- The expressions need to change based on selected Children in slicer. e.g:

CALCULATE(
    DISTINCTCOUNT(
        Table[RELATIVES]),
        FILTER(
        Table, Table[CHILDREN] IN {SELECTEDVALUE(FIRST_CHILD_SELECTION[CHILDREN])} || Data_Items[CHILDREN] IN {SELECTEDVALUE(SECOND_CHILD_SELECTION[CHILDREN])}
        )
)

Current approaches:

1. For shared expression, I have been able to get this done using actual tables (not virtual ones), and of course, not using slicers:

STEP 1:

COEXISTENCE_A_B_TEST = 
VAR Summarized_Table_ =
    SUMMARIZE(
        FILTER(
            Table,
            Table[CHILDREN] = "Peter" || Table[CHILDREN] = "Mary"
            ),
            Table[RELATIVE],
            Table[CHILDREN]
    )
RETURN
Summarized_Table_

STEP 2 ALTERNATIVE 1:

DISTINCTS = 
CALCULATE(
    COUNT(COEXISTENCE_A_B_TEST[RELATIVE]) - DISTINCTCOUNT(COEXISTENCE_A_B_TEST[RELATIVE]))

STEP 2 ALTERNATIVE 2:

COUNTS = 
COUNTROWS(
    FILTER(
        COEXISTENCE_A_B_TEST,
        EARLIER(COEXISTENCE_A_B_TEST[RELATIVE]) = COEXISTENCE_A_B_TEST[RELATIVE]
    )
)

DUPLICATES = 
DIVIDE(
    CALCULATE(
        COUNTROWS(
            COEXISTENCE_A_B_TEST),
            COEXISTENCE_A_B_TEST[COUNTS] > 1
),
2
)

Both alternatives 1 and 2 return the same result, but only work with static tables. I have not had the chance to make it work with virtual tables.

What I'm looking for:

- An expression that counts Relative coexistences within both Children A and B

- An expression that counts Relative exclusivity in Child A

- An expression that counts Relative exclusivity in Child B

Any ideas are really welcome, since I've exhausted all my creativity!!

Below an example of Table:

RELATIVE	CHILDREN	RELATIVE OCCUPATION**	CHILDREN AGE**
Rel 1	Child 1	Occ 1	Age 1
Rel 1	Child 2	Occ 1	Age 2
Rel 1	Child 5	Occ 2	Age 1
Rel 1	Child 9	Occ 6	Age 4
Rel 2	Child 3	Occ 2	Age 7
Rel 2	Child 2	Occ 6	Age 2
Rel 2	Child 6	Occ 4	Age 3
Rel 2	Child 1	Occ 3	Age 5

**To be used as additional filters

[dantesedmond · Accepted solution]

Solution:

Turns out there's a function called INTERSECT that does exactly what I was looking for.

And for exclusive Children, just take selected values and subtract intersection.