cancel
Showing results for
Did you mean:
Frequent Visitor

## Compare two fields by Coexistence and Exclusivity - DAX Approach

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

1 ACCEPTED SOLUTION
Frequent Visitor

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.

Frequent Visitor

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.

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!