DAX Occurrences of count

Question

So I have a table;

Now, I want to apply some dax instructions to that table, and display this data in a chart. In other words,  if it is the first time that the Country's name appears, it must show 1 and 2 for the second time.

Can anybody help me? Thanks.

Answer 1

Try this for creating a calculated column called Occurrences:

Occurrences =
CALCULATE (
    COUNT ( [Pais] ),
    FILTER (
        'Table',
        [Index] <= EARLIER ( 'Table'[Index] )
            && [Pais] = EARLIER ( 'Table'[Pais] )
    )
)

Index must be an incremental key in each row.

Let me know if this helps.

Comments

Hi,

This dax is taking too much time. I  tried on a data which has 5lakhs+ rows and the dax is showing 'working on it' for like half an hour.
Can you please help with some other similar dax function.

---

Hi, @Sandeep,

The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.

Syntax: COUNTROWS(<table>)

This function can be used to count the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table, or applying context to a table.

Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns zero if no rows are found that meet the conditions.

=COUNTROWS('Orders')

I hope this explanation will help.