Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Thanks Rena, but I don't think it is working.
I've added some numbers, and the algorithm misses. please see the picture below
Anyone?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |