Forum Discussion
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
- Anonymous4 years ago
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.
1 Reply
- AnonymousNot applicable
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.