Unified Customer ID - help with algorithm

[michaelsh]

Hello friends,

I have a dataset of over-phone purchases.

Each customer can call from different phone numbers and also pay with different credit cards.

I want to build an algorithm that "unites" all connected deals into one "United Customer ID", that can be for example the minimum of a credit card.

In the attached file for example, since there are only two unified customers.

Credir card 555555 is connected to 444444, 444444 is connected to 333333 and 333333 is connected to 111111 by phones, so all of them will get ID = 1111111

I feel that I need to to a self-join, but there can be several-steps connections as in the example.

Please help.

https://1drv.ms/x/s!AoP_9ampPIT7gaAdREap9NScHrEDUw?e=2fOI2G

[v-yiruan-msft]

Hi @michaelsh ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below to get the previous credit card

Precredit card = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[Phone] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Credit Card] = EARLIER ( 'Table'[Credit Card] )
        )
    )
RETURN
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Phone] IN _tab ),
        [Credit Card]
    )

2. Create a calculated column as below to get the min credit card

United Customer = PATHITEM ( PATH ( 'Table'[Credit Card], 'Table'[Precredit card] ), 1, INTEGER)

Best Regards

[michaelsh]

Thanks Rena, but I don't think it is working.

I've added some numbers, and the algorithm misses. please see the picture below

[michaelsh]

Anyone?