Dynamic Ranking within a category

[Anonymous]

Hi All,
I am trying to build a visual which shows dynamic rank for Patients falling in each category, and that ranking should get updated as per the Date filtered. This is the original rank based on category

Category	Patient	Date	Rank(based on Date)
a	ABC	1/1/2022	1
a	DEF	2/28/2022	2
b	ABC	1/1/2022	1
b	EFG	1/15/2022	2
b	DSF	2/15/2022	3

If i exclude 1/1/2022 from date filter, this is how the visual should look like:

Category	Patient	Date	Rank(based on Date)
a	DEF	2/28/2022	1
b	EFG	1/15/2022	1
b	DSF	2/15/2022	2

Thanks in Advance!

[Anonymous]

Just to Clarify, i need help with creating this Rank measure/ Column. I have either been able to create rank by Category that doesnt update as per date filter OR create dynamic rank that doesn't rank by category.  

[PowerUserR]

Hi,

I think this  thread hold your solution: https://community.powerbi.com/t5/Desktop/Dynamic-ranking-of-data-based-on-filter-selections-with/td-...

I hope that helps 🙂 

[Anonymous]

Hi, Thanks for helping. I tried creating calculated column(similar to Rank by Test Type in mentioned post) but i see only 1 getting populated for each row.

[amitchandak · Accepted solution]

@Anonymous , if you are creating a column Rank.

Rank = rankx(filter(Table, [Category] = earlier([Category]) , [Date],,asc,dense)

But I think you need a measure

Countx(filter(allselected(Table) , [Category] = Max([Category])  && [Date] <= Max([Date]) ), [Date])