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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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