Simple count of occurence of a name (cumulative)

[Joris_NL]

Hey hello,

Situation:

Species name	Index	Outcome needed
human	1	1
horse	2	1
fish	3	1
human	4	2
centaur	5	1
fish	6	2
fish	7	3

I only found suggestions in dax using 'EARLIER', which doesn't exist anymore and should be replaced with SELECTEDVALUE. Right? But I don't get it working:

Outcome = 
VAR _Selected = SELECTEDVALUE(Data[species name]) 

RETURN 
RANKX( 
    FILTER( 
        Data, Data[species name] = _Selected ), 
    Data[Index], ,
    ASC 
)

[AmiraBedh · Accepted solution]

Try the following :

Outcome =
CALCULATE (
    COUNTROWS ( Data ),
    FILTER (
        Data,
        Data[species name] = EARLIER ( Data[species name] )
        && Data[Index] <= EARLIER ( Data[Index] )
    )
)

[v-yilong-msft · Accepted solution]

Hi @Joris_NL ,

I create a table as you mentioned.

Then I create a Calculated column and it gives me the result you want. Here is the DAX code.

Outcome =
VAR CurrentIndex = 'Data'[Index]
VAR CurrentSpecies = 'Data'[Species name]
RETURN
    CALCULATE (
        COUNT ( 'Data'[Index] ),
        FILTER (
            ALL ( 'Data' ),
            'Data'[Index] <= CurrentIndex
                && 'Data'[Species name] = CurrentSpecies
        )
    )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AmiraBedh · Accepted solution]

Try the following :

Outcome =
CALCULATE (
    COUNTROWS ( Data ),
    FILTER (
        Data,
        Data[species name] = EARLIER ( Data[species name] )
        && Data[Index] <= EARLIER ( Data[Index] )
    )
)

[Joris_NL]

Thank you very much. Both suggestions work perfectly but this one, with EARLIER, calculates instantly while the sollution with variables takes about 15 seconds (though only once during refresh).