Help - Rank a dynamic column? I need the rank column for Lookup function.

[Anonymous]

I have a date column. I created an index column which starts from 1.

 For example:

Items  Rank

a             1

b            2

c            3

d           4

When I apply the filter- It shows:

Item   Rank

b          2

c          3

But I need something like this -

Item Rank

b         1

c          2

And I need to use that Rank column as a Lookup column to find other values.

[Anonymous]

hi @Anonymous ,

By using your data I write rank function please check two Measures and I shared two outputs also.

Please watch it.

--by using ALL function
Rank = RANKX(ALL('Table'),[Sum],,DESC,Skip)

-- by using ALLSELECTED function
Rank = RANKX(ALLSELECTED('Table'),[Sum],,DESC,Skip)

regards,

Naveen

[Anonymous]

Thanks for your reply.

---

What is the sum measure?

[Anonymous]

hi @Anonymous ,

Sum = SUM(Table[Number])

regards,

Naveen

[Anonymous]

Index are the numbers here. But I get 1s in all the column. I'm using index to rank the dates. But ranking dates in my ultimate aim.

Kindly let me know what went wrong.

Thanks,

Anand!

[Anonymous]

Also the filters are not working. the Req Id is connected to the Req number.

[az38]

Hi @Anonymous 

try smth like this

Measure = rankx(ALLSELECTED(Tbl_Rank);CALCULATE(FIRSTNONBLANK(Tbl_Rank[Items];Tbl_Rank[Items]));;ASC)

[Anonymous]

Hi,

Thanks for your reply!

But the code did not help.

I'm getting 1 for every row in the table.

It does not sort the remaining rows on the basics of start dates after applying the filters.

Thanks

[az38]

Hi @Anonymous 

It's strange because it works great with any filters applied

could you show all youe data model or *.pbix?

[Anonymous]

Thanks for your reply.

How do we attach our pbix file here?

[az38]

@Anonymous 

try cloud storage like https://uploadfiles.io/