Incorrect rows returned with countrows and filter function

[msft-cap]

I am using below DAX formulas to count the number of clients with Rating A to D. I am getting count as 8 even though the number of records is 5.

Any thoughts on what is going wrong here.

Client_count = Countrows(filter('ClientDetail','ClientDetail'[ClientRating] in { "A", "B", "C","D"}))

[jcalheir]

Hi

Try using a calculate function:

Client_count = 
CALCULATE(
    COUNTROWS('ClientDetail'),
    'ClientDetail'[ClientRating] in { "A", "B", "C","D"}
)

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[Jihwan_Kim]

Hi,

I think your measure seems correct.

Please check the below picture and the attached file.

I tried to create a sample pbix file like below. If your data model looks different, please share your sample pbix file's link here, and then I can try to look into it to come up with an accurate solution.

Thanks.

[msft-cap]

@Jihwan_Kim My report has about 400 records and only 5 records has clientRating either C or D. rest all are null. When I added the ClientRating field to table format, I get 5 records and rest all records has null/blank. I am not sure why I keep getting 8 count with the above DAX formula. 

I am unable to share report due to data confidentiality and mock up example just work fine as you shared. 

[Anonymous · Accepted solution]

Hi @msft-cap ,

Please make sure whether there are duplicate column combiantions like [Name] and [ClientRating] in your data model. 
For example: There may be two or more Name = C01 and ClientRating = C in your data model. Then you will count more than 5.

My Sample:

In table visual I could only see five records, due to Power BI will only show duplicates once. Your measure will return 8 in my sample.

Try this code.

Client_count =
COUNTROWS (
    SUMMARIZE (
        FILTER ( ClientDetail, 'ClientDetail'[ClientRating] IN { "A", "B", "C", "D" } ),
        ClientDetail[Name],
        ClientDetail[ClientRating]
    )
)

Result is as below.

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.