Customer with multiple accounts

[mosman]

I have a customer table with multiple columns, the main columns are:

SSN, AccountNumber, FirstName, LastName, MI, LastTransactionDate.

SSN is the unique identifier, because last name could change due to marriage/divorce 🙂

I would like to know the following:

1. count the customers who have multiple accounts period
2. count the customer who have multiple account and most recent transaction date.

how can do that in DAX?

[parry2k]

@mosman can you share sample data with expected result. remove sensitive info before sharing.

[mosman]

ssn	acountnumber	last_transaction_date
ab0ec3f97e	30934	8/31/2019 0:00
ab0ec3f97e	30934	8/31/2019 0:00
962116be86	38479	8/31/2019 0:00
aee38a3ef3	42478	6/30/2019 0:00
92eec1581d	43280	8/30/2019 0:00
2de6bf0f3a	44452	8/31/2019 0:00
962116be86	38479	6/30/2019 0:00
ab0ec3f97e	30934	8/31/2019 0:00
962116be86	38479	6/30/2019 0:00
21a9314597	39493	8/31/2019 0:00
1a4b1d6392	49225	9/1/2019 0:00
2de6bf0f3a	44452	8/31/2019 0:00
aee38a3ef3	42478	7/24/2019 0:00
ab0ec3f97e	30934	8/30/2019 0:00
962116be86	38479	8/31/2019 0:00
3100529bdb	46128	1/7/2019 0:00
7da3dc98cd	46362	8/31/2019 0:00
962116be86	38479	9/1/2019 0:00
d7341f7de9	16047	8/30/2019 0:00
af4ba3b4d0	56441	9/2/2019 0:00
e8a3c4bf3f	38053	6/29/2018 0:00
7cb6f2494c	50772	8/30/2019 0:00
2ff3c4bba2	57697	8/31/2019 0:00
962116be86	38479	8/31/2019 0:00
108452d158	31863	9/1/2019 0:00
b23f5c2ab6	56057	NULL
76fe0c3251	48408	8/29/2019 0:00
405037c96d	47103	8/31/2019 0:00
d93466af21	56606	8/31/2019 0:00
1a4b1d6392	49225	9/1/2019 0:00
0ab7d344e8	37942	10/15/2012 0:00
7ab48b7d82	57627	9/1/2019 0:00
962116be86	38479	8/31/2019 0:00
9e71b8471f	58311	8/28/2019 0:00
6422bba73a	58639	8/29/2019 0:00
962116be86	38479	8/31/2019 0:00
08fe7b0839	69051	8/31/2019 0:00
962116be86	38479	8/31/2019 0:00
38cf5e9f25	44891	6/23/2019 0:00
95d487dcb6	50766	6/30/2019 0:00
962116be86	38479	3/18/2018 0:00

[v-lid-msft]

Hi @mosman ,

Based on your requirement, we can create the following three measures.

MultiPeriodAccount =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( ALL ( Table1 ), Table1[ssn] ),
            "PeriodCount", COUNTROWS (
                (
                    DISTINCT ( FILTER ( ALL ( Table1 ), 'Table1'[ssn] = EARLIER ( Table1[ssn] ) ) )
                )
            )
        ),
        [PeriodCount] > 1
    )
)

MultiAccount =
VAR result =
    COUNTROWS (
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( ALL ( Table1 ), 'Table1'[ssn] = SELECTEDVALUE ( 'Table1'[ssn] ) ),
                "a", [last_transaction_date]
            )
        )
    )
RETURN
    IF ( result > 1, result, BLANK () )

RecentDate =
IF (
    [MultiAccount] = BLANK (),
    BLANK (),
    MAXX (
        SELECTCOLUMNS (
            FILTER ( ALL ( Table1 ), 'Table1'[ssn] = SELECTEDVALUE ( 'Table1'[ssn] ) ),
            "a", [last_transaction_date]
        ),
        [a]
    )
)

If it doesn't meet your requirement, kindly share your  excepted result based on your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[mosman]

Hello Parry2k,

I would like to get the count of ssn with multiple account. if I look at the data I shared with you, we should have the result 5 as shown below:

08fe7b0839	1
0ab7d344e8	1
108452d158	1
1a4b1d6392	2
21a9314597	1
2de6bf0f3a	2
2ff3c4bba2	1
3100529bdb	1
38cf5e9f25	1
405037c96d	1
6422bba73a	1
76fe0c3251	1
7ab48b7d82	1
7cb6f2494c	1
7da3dc98cd	1
92eec1581d	1
95d487dcb6	1
962116be86	10
9e71b8471f	1
ab0ec3f97e	4
aee38a3ef3	2
af4ba3b4d0	1
b23f5c2ab6	1
d7341f7de9	1
d93466af21	1
e8a3c4bf3f	1

[mussaenda]

Hi @mosman ,

[mosman]

I am looking for  how many ssn that have more than one accountnumber. is that what the formula for?

[amitchandak · Accepted solution]

I think you are looking at the grand total, the same as what is done is this issue 

https://community.powerbi.com/t5/Desktop/Count-number-of-rows-each-value-appears-influenced-by-date/...

SUMX (
DISTINCT ( Table1[SSN] ),
INT ( CALCULATE ( COUNT ( Table1[Account No] ) ) >= 1 )
)