Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
)
Solved! Go to Solution.
Try the following :
Outcome =
CALCULATE (
COUNTROWS ( Data ),
FILTER (
Data,
Data[species name] = EARLIER ( Data[species name] )
&& Data[Index] <= EARLIER ( Data[Index] )
)
)
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.
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.
Try the following :
Outcome =
CALCULATE (
COUNTROWS ( Data ),
FILTER (
Data,
Data[species name] = EARLIER ( Data[species name] )
&& Data[Index] <= EARLIER ( Data[Index] )
)
)
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).
User | Count |
---|---|
139 | |
70 | |
67 | |
52 | |
52 |
User | Count |
---|---|
210 | |
92 | |
64 | |
59 | |
56 |