from Rankx Calculated Column to Measure

[johanthedataman]

Hi guys,

I'm really struggling with the following: How can I write the following Rankx Calculated Column as a Measure?

Rank =

RANKX (

  FILTER (

  Table,

  Table[organisation_id] = EARLIER ( Table[organisation_id] )

  ),

  Table[Index],

  ,

  ASC,

  DENSE

)

This should be the expected outcome. I have this as a calculated column, but due to performance-issues I need this as a measure.

organisation_id	Index	Rank
1	1	1
1	2	2
1	3	3
2	4	1
2	5	2
3	6	1
3	7	2
3	8	3
4	9	1
5	10	1
5	11	2
5	12	3
5	13	4

[nandukrishnavs · Accepted solution]

@johanthedataman 

Try this

IndexValue = SUM('Table'[Index])

Rank = 
var _selectedID= SELECTEDVALUE('Table'[organisation_id])
var _rank=RANKX(FILTER(ALL('Table'),'Table'[organisation_id]=_selectedID),[IndexValue])
return _rank

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[az38]

Hi @johanthedataman 

have a look at this rankx textbook https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[johanthedataman]

Thanks. So far read and tried, it didn't solve my problem. 

[nandukrishnavs · Accepted solution]

@johanthedataman 

Try this

IndexValue = SUM('Table'[Index])

Rank = 
var _selectedID= SELECTEDVALUE('Table'[organisation_id])
var _rank=RANKX(FILTER(ALL('Table'),'Table'[organisation_id]=_selectedID),[IndexValue])
return _rank

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[nandukrishnavs · Accepted solution]

@johanthedataman 

Try this

IndexValue = SUM('Table'[Index])

Rank = 
var _selectedID= SELECTEDVALUE('Table'[organisation_id])
var _rank=RANKX(FILTER(ALL('Table'),'Table'[organisation_id]=_selectedID),[IndexValue],,ASC)
return _rank

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[johanthedataman]

Great, @nandukrishnavs you made my day. 

[nandukrishnavs]

@johanthedataman  please mark my reply as accepted solution.

[johanthedataman]

yes, I did, thanks!