CODEWITHSUNDEEP
powerbidaxcountif
johon
johon

Reputation: 65

COUNTIF in Power BI (New calculated column Required)

I want to calculate the running count of each value based on column A. In Excel, I am applying the following formula

=COUNTIF($A$2:$A2,A2)

I would like to get the same result in Power BI. Can you please advise.

enter image description here

Upvotes: 0

Views: 1136

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

With just a single column, this is impossible in DAX because duplicated rows cannot be distinguished as there is no inherent order to a column.

However, if you have an index column on the table (you can easily add one in the Query Editor), then it is possible to define such a calculated column so that it works similarly to the Excel formula.

CountIf =
VAR CurrentIndex = DATA[Index]
RETURN
    CALCULATE (
        COUNTROWS ( DATA ),
        ALLEXCEPT ( DATA, DATA[ITEM] ),
        DATA[Index] <= CurrentIndex
    )

Upvotes: 1

Related Questions