Count rows with same code

[jlarques]

Dear community,

I have a fact table with same country code in many rows, and I want to create a new calculated column that:

• If country code appears one time, new column will have 0
• If country code appears two times, new column will have 1 
• If country code appears three times, new column will have 2, etc. 

How can I do it? In DAX language or in Power Query? I have tried many times but I have not succeeded. 

Can you help me? 

Kind regards,

José Luis 

[ryan_mayu · Accepted solution]

@jlarques 

you can try to create an index column in pq

then use DAX to create a column

Column = CALCULATE(count('Table'[country]),FILTER('Table','Table'[country]=EARLIER('Table'[country])&&'Table'[Index]<=EARLIER('Table'[Index])))

if you have a datetime column, you can use that instead of the index column

[ryan_mayu · Accepted solution]

@jlarques 

you can try to create an index column in pq

then use DAX to create a column

Column = CALCULATE(count('Table'[country]),FILTER('Table','Table'[country]=EARLIER('Table'[country])&&'Table'[Index]<=EARLIER('Table'[Index])))

if you have a datetime column, you can use that instead of the index column

[jlarques · Accepted solution]

Hi @ryan_mayu ,

thanks for your answer. I tested and it works! It's amazing!. I would never have achieved it and I am extremely grateful that knowledge can be shared in this community. Thank you very much for your help and @v-kongfanf-msft  and @vicky_ who have also wanted to help me with this, for me, difficult problem.

Thanks again to this community.

José Luis 

[ryan_mayu]

you are welcome

[v-kongfanf-msft]

Thanks for the reply from @vicky_ , please allow me to provide another insight:

Hi @jlarques ,

Maybe create  calculated column is a good and easy choose. You can try below formula:

Column =
VAR CUR_ = 'Table'[Country Code]
RETURN
    COUNTROWS ( FILTER ( ALL ( 'Table' ), 'Table'[Country Code] = CUR_ ) ) - 1

Best Regards,
Adamk Kong

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

[jlarques]

Hi @vicky_ and @v-kongfanf-msft ,

thanks for your help. The @vicky_ solution gives me one everytime that same country code appears, not increase the number of times that appears.

The @v-kongfanf-msft solution gives me the total times that the country code appears. So, these are not exactly the solution that I'm looking for. In your screenshot,every time that appears a country counts the total times that this country appears and my idea is that gives me the number of times that appears the country in this row. For instance, first time that appears GER would be 0, second time would be 1, next time would be 2, etc., etc.

Thanks again.

Best regards, 

José Luis

[vicky_]

You can create a DAX column (or measure) with:
Count of Country Code = CALCULATE(COUNT(Table[Country Code]), ALLEXCEPT(Table, Table[Country Code]))