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 recogni...
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.

     

VasTg's avatar
VasTg
Icon for Memorable Member rankMemorable 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
    Icon for Community Support rankCommunity 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.