locked
DAX expression to count values within a table RRS feed

  • 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])))

     

    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])))

     

    Monday, February 28, 2011 8:11 AM
  • First one worked like a charm!!  Thanks
    Thanks, Barbara
    Monday, February 28, 2011 4:02 PM