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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Joris_NL
Helper II
Helper II

Simple count of occurence of a name (cumulative)

Hey hello,

 

Situation:

 

Species nameIndexOutcome needed
human11
horse21
fish31
human42
centaur51
fish62
fish73

 

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 
)

 

 

 

 

 

2 ACCEPTED SOLUTIONS
AmiraBedh
Super User
Super User

Try the following :

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

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

v-yilong-msft
Community Support
Community Support

Hi @Joris_NL ,

I create a table as you mentioned.

vyilongmsft_0-1713411999736.png

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

vyilongmsft_1-1713412450966.png

 

 

 

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.

View solution in original post

3 REPLIES 3
v-yilong-msft
Community Support
Community Support

Hi @Joris_NL ,

I create a table as you mentioned.

vyilongmsft_0-1713411999736.png

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

vyilongmsft_1-1713412450966.png

 

 

 

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
Super User
Super User

Try the following :

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

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.