Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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).
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
59 | |
59 |