Forum Discussion

puru85's avatar
puru85
Helper II
2 years ago
Solved

Creating Family Keys Based on Shared Address or Phone Number

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!

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • NaveenGandhi's avatar
    NaveenGandhi
    Memorable Member
    2 years ago

    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!!!

  • puru85's avatar
    puru85
    Helper II
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
      NaveenGandhi
      Memorable Member
      2 years ago

      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