topic Re: Similar to countif in Excel in DAX Commands and Tips

Similar to countif in Excel

Metstone — Thu, 22 Aug 2019 11:41:24 GMT

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

Re: Similar to countif in Excel

AlB — Thu, 22 Aug 2019 12:45:03 GMT

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

Re: Similar to countif in Excel

Metstone — Thu, 22 Aug 2019 13:06:13 GMT

Thanks!! That's exactly what I needed.