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.

     

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.