Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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.
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]))
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?
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.
This is an awesome idea! I learnt something new today. Thank you!
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?
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.