How can I count the number of repeating CustomerID in new column

[ligalbert]

Hi, I want to count the number of repeating CustomerID. For example CustomerID 8 repeats many times. How can I create a measure that counts the number CustomerID Repeats. 

 

[v-rzhou-msft · Accepted solution]

Hi @ligalbert ,

The way to count repeating CustomerID is based on your data model. Are [CustomerID] and [Loan ID] in the same table? If they are in the same table, [CustomerID] will repeat in data model. I think you just need to use count function.

Measure:

Count repeating CustomerID =
CALCULATE ( COUNT ( 'Table'[CustomerID] ) )

If [CustomerID] and [Loan ID] are not in the same table and [CustomerID] will repeat by expanding in visual, yu can try to create a virtual table in your measure. 

Measure should look like as below. Column in Filter field in "CustomerID" part should be the related columns which could help us determind CustomerID. If there are multiple columns in filter, we can use AND OR && between them.

Count repeating CustomerID =
VAR _SUMMARZIE =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[LoanID],
        "CustomerID",
            CALCULATE (
                MAX ( 'Table2'[CustomerID] ),
                FILTER ( 'Table2', 'Table2'[column] = EARLIER ( 'Table'[column] ) )
            )
    )
RETURN
    COUNTAX (
        FILTER ( _SUMMARZIE, [CustomerID] = MAX ( 'Table2'[CustomerID] ) ),
        [LoanID]
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit_singh]

Hi @ligalbert ,

Try this 

SUMX(<table_name>, if(EARLIER(<table_name>[CustomerId]) = <table_name>[CustomerId], 1,0))

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Hi,

calculate(count(CustomerID),all(LoanID))

it should works