Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mosman
Frequent Visitor

Customer with multiple accounts

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?

1 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 )
)

 

 

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

ssnacountnumberlast_transaction_date
ab0ec3f97e309348/31/2019 0:00
ab0ec3f97e309348/31/2019 0:00
962116be86384798/31/2019 0:00
aee38a3ef3424786/30/2019 0:00
92eec1581d432808/30/2019 0:00
2de6bf0f3a444528/31/2019 0:00
962116be86384796/30/2019 0:00
ab0ec3f97e309348/31/2019 0:00
962116be86384796/30/2019 0:00
21a9314597394938/31/2019 0:00
1a4b1d6392492259/1/2019 0:00
2de6bf0f3a444528/31/2019 0:00
aee38a3ef3424787/24/2019 0:00
ab0ec3f97e309348/30/2019 0:00
962116be86384798/31/2019 0:00
3100529bdb461281/7/2019 0:00
7da3dc98cd463628/31/2019 0:00
962116be86384799/1/2019 0:00
d7341f7de9160478/30/2019 0:00
af4ba3b4d0564419/2/2019 0:00
e8a3c4bf3f380536/29/2018 0:00
7cb6f2494c507728/30/2019 0:00
2ff3c4bba2576978/31/2019 0:00
962116be86384798/31/2019 0:00
108452d158318639/1/2019 0:00
b23f5c2ab656057NULL
76fe0c3251484088/29/2019 0:00
405037c96d471038/31/2019 0:00
d93466af21566068/31/2019 0:00
1a4b1d6392492259/1/2019 0:00
0ab7d344e83794210/15/2012 0:00
7ab48b7d82576279/1/2019 0:00
962116be86384798/31/2019 0:00
9e71b8471f583118/28/2019 0:00
6422bba73a586398/29/2019 0:00
962116be86384798/31/2019 0:00
08fe7b0839690518/31/2019 0:00
962116be86384798/31/2019 0:00
38cf5e9f25448916/23/2019 0:00
95d487dcb6507666/30/2019 0:00
962116be86384793/18/2018 0:00

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]
    )
)

6.PNG

 


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 

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

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:

08fe7b08391
0ab7d344e81
108452d1581
1a4b1d63922
21a93145971
2de6bf0f3a2
2ff3c4bba21
3100529bdb1
38cf5e9f251
405037c96d1
6422bba73a1
76fe0c32511
7ab48b7d821
7cb6f2494c1
7da3dc98cd1
92eec1581d1
95d487dcb61
962116be8610
9e71b8471f1
ab0ec3f97e4
aee38a3ef32
af4ba3b4d01
b23f5c2ab61
d7341f7de91
d93466af211
e8a3c4bf3f1
mussaenda
Community Champion
Community Champion

Hi @mosman ,

 

2019_09_11_18_33_51_Untitled_Power_BI_Desktop.png

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

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

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.