locked
Calculating a count of rows where value matches another column value in the same table RRS feed

  • Question

  • Hi,

    I'm struggling to do something in DAX that seems to me should be super easy (coming from a SQL world)!

    That is to count all rows in column 1 where the value matches the current value for column 1?

    E.g something like this:

    [Col2]=Count of rows in [Col1] where value = this.[Col1]

    Where the results are as in the table below:

    Col1, Col2

    A, 2

    A, 2

    B, 1


    Anyone?


    Martin Laukkanen
    Nearbaseline blog - nearbaseline.com/blog
    Bulk Edit and other Apps - nearbaseline.com/apps

    Wednesday, May 14, 2014 3:51 PM

Answers

  • Try this as a calculated column:

    =CALCULATE(
        COUNTROWS(TableName),
        FILTER(
            TableName,
            TableName[Col1] = EARLIER(TableName[Col1])
        )
    )


    Wednesday, May 14, 2014 4:17 PM
    Answerer

All replies

  • Try this as a calculated column:

    =CALCULATE(
        COUNTROWS(TableName),
        FILTER(
            TableName,
            TableName[Col1] = EARLIER(TableName[Col1])
        )
    )


    Wednesday, May 14, 2014 4:17 PM
    Answerer
  • Thanks, that's perfect! 

    I knew it had to be something so simple, but after spending over an hour banging my head against those exact functions I couldn't get anything working!


    Martin Laukkanen
    Nearbaseline blog - nearbaseline.com/blog
    Bulk Edit and other Apps - nearbaseline.com/apps

    Thursday, May 15, 2014 8:03 AM