Answered by:
DAX expression to count values within a table
Question

I have a PowerPivot table 'table' which has a column [n_country]. within the PowerPivot table, I would like to calculate the count of how many time the value of [N_Country] on the current row appears in the entire table. Basically what I am looking for is the PowerPivot equivalent to a COUNTIF equivalent.
I have tried all types of formulas, but cannot get what I am looking for. If "Bolivia" appears 23 times in the table, then for this new column, I want the value of the current row to be 23.
I have tried several combinations of CountAx, Calculate and Filter, but cannot find the correct syntax. Also, I do know that I can get this number easily in a pivot, but I need this calculated number as an interim step in a larger calculation.
Any ideas??
Friday, February 25, 2011 11:10 PM
Answers

Here are two formulas:
=CALCULATE(COUNTROWS('table'), ALLEXCEPT('table', 'table'[n_country]))
OR
=COUNTROWS(FILTER('table', [n_country] = EARLIER([n_country])))
 Proposed as answer by Javier GuillenEditor Monday, February 28, 2011 6:57 PM
 Marked as answer by Barbara Raney Thursday, March 10, 2011 6:56 PM
Monday, February 28, 2011 8:11 AM
All replies

Try using the VALUES function.Saturday, February 26, 2011 3:32 AM

Here are two formulas:
=CALCULATE(COUNTROWS('table'), ALLEXCEPT('table', 'table'[n_country]))
OR
=COUNTROWS(FILTER('table', [n_country] = EARLIER([n_country])))
 Proposed as answer by Javier GuillenEditor Monday, February 28, 2011 6:57 PM
 Marked as answer by Barbara Raney Thursday, March 10, 2011 6:56 PM
Monday, February 28, 2011 8:11 AM 
First one worked like a charm!! Thanks
Thanks, BarbaraMonday, February 28, 2011 4:02 PM