Counting number of occurrences in a column and have the result in a separate column

[mderraz]

Hi guys,

I have a column with countries and would like to count occurences in a spearate colum as shown below:

Countries        Occurence Count

France                           1

England                         1

Germany                       1

France                           2 

China                            1

France                           3

How can I create the "Occurence Count" column please?

Thanks!

[Zubair_Muhammad · Accepted solution]

@mderraz 

First add an Index Column from Query Editor

Then you can Rank that Index for each Country to get the Occurrence Count as follows

Occurence Count =
RANKX (
    FILTER ( Table1, Table1[Countries] = EARLIER ( Table1[Countries] ) ),
    [Index],
    ,
    ASC
)

[mderraz]

Thanks Zubair! It worked perfectely, I didn't know Inderx column as I am still new using with Power BI.

One question though: my Dashboard takes now a lot more time to Refresh, could it be because of the Index column I added.

Seems like it's taking a lot of memory. Or is it something else?