Forum Discussion

bednarol's avatar
bednarol
New Member
6 years ago
Solved

Count occurrence

I want to add a column and count how many times a certain Program Name occurs in column "Program" in table "Dimension". I tried to add custom column and following formula but get "COUNTX" not recognized.

 

=COUNTX(FILTER(EARLIER([Program])=[Program]),[Program])

 

Thanks!

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi bednarol 

    In my first solution of the previous reply, i use "Group" function, but it would not change the data model, 

    please note, i add two "operations",

    one is "count"(count rows) based on "program" column, 

    another is "all"(don't summarized), it means it would be the original columns.

    then i get two columns, one is "count", another is "all", 

    in "all" column, there are all my original columns nested here,

    i click on "expand" icon from the "all" column and select columns i need, i can get all original columns.

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

7 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    bednarol 

     

    Try this..

     

    Column = COUNTX(FILTER(Dimension,EARLIER(Dimension[Program])=Dimension[Program]),Dimension[Program])

     

    If this helps, accept it as a solution.

    Kudos are nice too.

     

    • bednarol's avatar
      bednarol
      New Member
      6 years ago

      I am getting "COUNTX' not recognized. I should make clear that I am trying to use Power Query in Excel, not Power BI Desktop since the user who will be using this does not have desktop. I tried to enable addins that seem to be relevant. Is it something that is not available in Power Query?

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi bednarol 

        Your formula with "COUNTX' is a DAX formula, which is used in a measure or a calculated column in Power Pivot.

        If you just use it in Power Query->Add a custom column, it should not work as default.

        In Power Query, you can refer to my first solution in my previous reply.

         

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi bednarol 

    In Edit queries, you can group rows and add a count column.

    Or, create a calculated column in Data model view,

     

    Column = CALCULATE(COUNT('Table'[program]),ALLEXCEPT('Table','Table'[program]))

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • bednarol's avatar
      bednarol
      New Member
      6 years ago

      Grouping is obvious but I do not want to do that because I need to keep the table ungroup for futher data manipulation. Using the calculated column in Desktop in a good idea but I cannot figure out how to do that in Excel Power Query. Maybe that is one of the limitation?

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi bednarol 

        In my first solution of the previous reply, i use "Group" function, but it would not change the data model, 

        please note, i add two "operations",

        one is "count"(count rows) based on "program" column, 

        another is "all"(don't summarized), it means it would be the original columns.

        then i get two columns, one is "count", another is "all", 

        in "all" column, there are all my original columns nested here,

        i click on "expand" icon from the "all" column and select columns i need, i can get all original columns.

        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.