how to count values starts with RR

[abc_777]

hi,

I have table data like

Customer ID

1045

2014

3056

RR34544

RR34565

RR57585

RR89999

 etc

I have done advansed filter and begin with RR so I get

Customer ID

RR34544

RR34565

now I want to count these values starts with RR

[amitchandak · Accepted solution]

@abc_777 , a new measure 

countrows(Filter(Table, left(Table[Customer ID],2) = "RR"))

[abc_777 · Accepted solution]

I have tried follwoing two two formulas but not giving me correct result.

if see the jpeg image i attached its not counting correctly. I have values two time but it counting one

CR Count = COUNTROWS(

    FILTER(Table, left(Column,2) = "CR"))

Count of CR=

CALCULATE(

    COUNTROWS(Table),

    LEFT(column,2)

        IN { "CR" }

)

See the difference

[Anonymous · Accepted solution]

Hi @abc_777 ,

Please follow these steps:

1. Set a flag to specify rows which starts with RR

Filter RR = IF(LEFT(MAX('Table'[Customer ID]),2)="RR",1)

2. Apply the measure to filter pane like this: 

3. Count the number of eligible rows:

Count = SUMX('Table',[Filter RR])

The final output is shown below:

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

[abc_777]

@Anonymous and @amitchandak 

thanks

perfect and its working great. also amitchandak one is wokring. I compaire and found the issue I had. thanks both of you

[amitchandak · Accepted solution]

@abc_777 , a new measure 

countrows(Filter(Table, left(Table[Customer ID],2) = "RR"))

[abc_777]

Hi @amitchandak ,

I have tried your solution. BUt something I have made mistake. In matrix the actual count is 3 but it shows 8 in KPI(there I give the formaula). Please have a look and advise

[abc_777]

tried many ways but its not counting correctly

[abc_777]

any solution? 

[abc_777]

@Anonymous,

would you be able to give me the solution on this issue please

[abc_777 · Accepted solution]

I have tried follwoing two two formulas but not giving me correct result.

if see the jpeg image i attached its not counting correctly. I have values two time but it counting one

CR Count = COUNTROWS(

    FILTER(Table, left(Column,2) = "CR"))

Count of CR=

CALCULATE(

    COUNTROWS(Table),

    LEFT(column,2)

        IN { "CR" }

)

See the difference