Creating Family Keys Based on Shared Address or Phone Number

[puru85]

Hello Experts,

I am working on a DAX formula to create family keys in my customer table by grouping records based on shared addresses or phone numbers.

Current Logic: I am using the following DAX formula to generate a family key based on shared addresses or phone numbers. However, this logic does not group the IDs correctly when multiple criteria are met across different records.

Family Key =
CALCULATE(
MIN('Customer_FK'[id]),
FILTER(
'Customer_FK',
'Customer_FK'[Address] = EARLIER('Customer_FK'[Address]) ||
'Customer_FK'[Phone] = EARLIER('Customer_FK'[Phone])
)
)

Issue:

For instance, Ids 1 and 3 share the same address, and Ids 6 and 7 share the same address. Moreover, Id 5 shares the same phone number as Id 3. Ideally, IDs 1, 3, 5, 6, and 7 should be grouped as one family due to these shared connections.

Attahced is the PBIX:
FamilyKey_1.pbix

I appreciate any guidance or solutions you can provide.

Thank you!

[puru85]

Hi @NaveenGandhi ,

Below is the expected output. ID: 1,3,5,6,7 to grouped under one family key. ID: 11 and 12 to rouped under one family key and  ID: 13 and 14 to rouped under one family key.

Thank you!

Thank you!

[Anonymous · Accepted solution]

Hi @puru85 

The link you provided can not be opened:

Here I create a set of sample:

Then add a calculated column:

Outcome =
VAR _VTABLE =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "_MinAddress",
            CALCULATE (
                MIN ( 'Table'[Id] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Address] = EARLIER ( 'Table'[Address] )
                )
            )
    )
RETURN
    MINX (
        FILTER ( _VTABLE, 'Table'[phone] = EARLIER ( 'Table'[phone] ) ),
        [_MinAddress]
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NaveenGandhi]

@puru85 

I still don't understand the logic behind grouping 6,7 with 1,2,3,5. There is no pattern that we can use to write DAX.

Can you give me more information on this.

Regards,

NG

[NaveenGandhi]

HI @puru85 

Can you comfirm if 1,2,3,5,6,7 should be goruped together as one family or 1,2,3,5 should be grouped as they share something in common. Please provide a screenshot of expected output for better understanding,

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!