Forum Discussion

mosman's avatar
mosman
Frequent Visitor
6 years ago
Solved

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?

Need Help

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

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

    • mosman's avatar
      mosman
      Frequent Visitor
      6 years ago
      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
      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        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