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.
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.
Solved! Go to 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.
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.
Hi @ligalbert ,
Try this
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
Hi,
calculate(count(CustomerID),all(LoanID))
it should works
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 |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |