Dynamic Rank of a Text column

[Anonymous]

Hi All,

  I am trying to find the rank of a column(Full Name) dynamically on selection of each slice(LevelName) which is of type Text.

  Any suggestion to get it.

Thanks,

Sud

[v-jiascu-msft]

Hi @Anonymous,

The question is how to rank the [FullName]? For example, if we choose "Team Zuid West", how to rank the 10 FullNames? Who is the first, the second... ?

Usually, there could be another column of values. 

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft,

  I need to have another column which will give me rank/index which changes dynamically.

  On first load I need all the FullNames to be indexed from 1 in any order and where on click of any team I need the team members corresponding to that team starting with index 1.

  I hope I am clear here.

Thanks,

Sud

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

1. Add an index column in the Query Editor,

2. Create a measure like this.

Measure =
RANKX ( ALL ( 'Table1'[FullName] ), CALCULATE ( SUM ( 'Table1'[Index] ) ) )

3. You can try out in this file.

Best Regards,

Dale

[MarcelBeug]

My suggestion would be to rephrase your question and provide more details.

[Anonymous]

Hi @MarcelBeug,

 I have updated my question and attached a sample.

 Let me know if you need anything else.

Thanks,

Sud

[MarcelBeug]

Some expected results would be helpful, but maybe you are looking for this DAX calculated column:

Rank = RANKX(FILTER(Table1, Table1[LevelName] = EARLIER(Table1[LevelName])),Table1[FullName])

[Anonymous]

Hi @MarcelBeug,

  I get error when using EARLIER DAX function.

Thanks,

Sud

[MarcelBeug]

Probably your culture has a different delimiter.

[Anonymous]

Hi @MarcelBeug,

  I have loaded the data directly SQL server. So not sure if anything is missing here.

Thanks,

Sud

[MarcelBeug]

Some information from you is missing here, like the error message you get.

Anyhow, what I meant is that you probably need to replace the comma delimiters by semicolons:

Rank = RANKX(FILTER(Table1; Table1[LevelName] = EARLIER(Table1[LevelName]));Table1[FullName])

[Anonymous]

Hi @MarcelBeug,

  From my end comma delimiter works fine. Here is the below error I get when I try to have the EARLIER function.

Thanks,

Sud

[MarcelBeug]

Try and replace Levelname by LevelName.

[Anonymous]

hI @MarcelBeug,

  I was putting the rank formula in a Measure so I was getting error. Now I get the rank but the same rank comes for many users.

i tried using dense in Rank function.

Thanks,
Sud

[MarcelBeug]

I can't see nothing wrong in the information you provided.

So it must be something in the information you didn't provide (again...).