Forum Discussion

jlarques's avatar
jlarques
Helper V
2 years ago
Solved

Count rows with same code

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 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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's avatar
    jlarques
    2 years ago

    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 Anonymous  and vicky_ who have also wanted to help me with this, for me, difficult problem.

     

    Thanks again to this community.

     

    José Luis 

6 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

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

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    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's avatar
      jlarques
      Helper V
      2 years ago

      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 Anonymous  and vicky_ who have also wanted to help me with this, for me, difficult problem.

       

      Thanks again to this community.

       

      José Luis 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      jlarques
      Helper V
      2 years ago

      Hi vicky_ and Anonymous ,

       

      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 Anonymous 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