How to create a measure that ranks from the sort order of a text column (NOT numerical column).

[SamuelO]

Hello PowerBI Community,

I am new to PowerBI and trying out a few things here and would like your support.

I created a column rank that ranks my weekly dates as seen below;

The challenge that Iam facing however is that I have measures that I am using to calculate the week over week counts difference and since I created WeekSortRank as column, it does not change dynamically depending on the filter, as seen below;

As you can see above, when filters are applied, the week old ranks are still maintained because it was a column and not a measure, and in the end, WeekSortEnrollmentLastWeek also ends up skipping some of the previous WeekSortEnrollement which is ideally supposed to pick from.

Therefore, I would like to have the WeekSortRank to be created by a measure since this adjusts well with filters and I believe this will inturn solve the WeekSortEnollmentLastWeek.

Cuurent  code that I used to create the above columns

WeekSortRank = RANKX(allselected('Data Table'), 'Data Table'[weekly_dates],,ASC,Dense)

WeekSortEnrollment = calculate(COUNTROWS(VALUES('Data Table'[Email])), FILTER('Data Table'[WeekSortRank]=MAX('Data Table'[WeekSortRank])))

WeekSortEnrollmentLastWeek = CALCULATE([WeekSortEnrollment], FILTER(ALLSELECTED('Data Table'),

'Data Table'[WeekSortRank] = MAX('Data Table'[WeekSortRank])-1))

Can I create this column (WeekSortRank = RANKX(allselected('Data Table'), 'Data Table'[weekly_dates],,ASC,Dense)) as a mesure?

Thank you all?

[v-yalanwu-msft · Accepted solution]

Hi, @SamuelO ,

If you want to create a measure.

rankmeasure = RANKX(ALLSELECTED('Table'),CALCULATE( MAX('Table'[Date])),,ASC)

The final show:

sort by text column is okey

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SamuelO]

Thanks @v-yalanwu-msft.

The code above worked fine for me by adding the ,dense.

I appreciate your time.

[amitchandak]

@SamuelO , If you are looking for this week Vs Last week and you have selected a week in the slicer , then you all not allselected

WeekSortEnrollmentLastWeek = CALCULATE([WeekSortEnrollment], FILTER(ALL('Data Table'),
'Data Table'[WeekSortRank] = MAX('Data Table'[WeekSortRank])-1))

refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[SamuelO]

Thank you so much @amitchandak for your time on this.

So for the WeekSortRank above, how can I create a measure that ranks basing on the alphabetical sorting?