Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Metstone
Frequent Visitor

Similar to countif in Excel

Hi, I have seen quite a few posts relating to ways of emulating COUNTIF in excel but none of them seem to match what I am after. I have a table in Power BI Desktop I need to add a column that shows an incremental count each time a duplicate value is found in another column. Any help would be appreciated.

 

RefNumber

NewColumn

2254

1

2255

1

2255

2

2255

3

2256

1

2256

2

2257

1

2258

1

2259

1

2259

2

2259

3

2259

4

2260

1

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Metstone 

First you need to have a column that establishes an order. Go to the query editor and add an index column (Add Column -> Add Index column)

Then in DAX you can create a new calculated column:

NewCol =
CALCULATE (
    COUNT ( Table1[RefNumber] ),
    ALLEXCEPT ( Table1, Table1[RefNumber] ),
    Table1[Index] <= EARLIER ( Table1[Index] )
)

 

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @Metstone 

First you need to have a column that establishes an order. Go to the query editor and add an index column (Add Column -> Add Index column)

Then in DAX you can create a new calculated column:

NewCol =
CALCULATE (
    COUNT ( Table1[RefNumber] ),
    ALLEXCEPT ( Table1, Table1[RefNumber] ),
    Table1[Index] <= EARLIER ( Table1[Index] )
)

 

Metstone
Frequent Visitor

Thanks!! That's exactly what I needed.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors