Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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:

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

**To be used as additional filters

  • Anonymous's avatar
    Anonymous
    4 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

  • Anonymous's avatar
    Anonymous
    Not 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.