topic Re: Help with RANKX in DAX Commands and Tips

Help with RANKX

NB689 — Mon, 24 Oct 2022 22:46:42 GMT

I have looked through some other threads and articles online but I can't sort out what my issue is that I'm having with RANKX.

My goal is to have a ranking that ranks by user ID, and will auto update the ranking based on the filters applied to my table.

I have am using this formula for my RankX:

RANKX = RANKX(ALLSELECTED('Non Overlapping') , [Average Personal Time],,DESC,Dense )

Here you can see the results for my "RANKX" column aren't coming out correctly. My intended output would be row 1 to be 1, then 2, 3, etc.

Thank you

Re: Help with RANKX

Jihwan_Kim — Tue, 25 Oct 2022 03:20:56 GMT

Hi,

I cannot know how your datamodel looks like, but please try something like below.

Or, please share your sample pbix file's link.

RANKX measure = RANKX ( SUMMARIZE ( ALLSELECTED ( 'Non Overlapping' ), 'TableName1'[Group], 'TableName2'[Region], 'TableName3'[Division], 'TableName4'[UserID], 'TableName5'[Specialities] ), [Average Personal Time], , DESC, DENSE )

Re: Help with RANKX

NB689 — Fri, 28 Oct 2022 14:42:59 GMT

Here's what our data looks like. The file is rather large right now and hase some sensitive data, but if you need more information I can provide that.

The measure for Average Personal Time looks like this:

Average Personal Time 3 = IFERROR( SUM('Non Overlapping'[After Hours]) / DISTINCTCOUNT('Non Overlapping'[Login Number]) / (CALCULATE(DISTINCTCOUNT('Non Overlapping'[User ID]), FILTER('Non Overlapping', 'Non Overlapping'[After Hours]>0)) +0) , 0)

Re: Help with RANKX

NB689 — Mon, 31 Oct 2022 16:46:13 GMT

This took some trial and error, but I ended up solving my issue. One change I am going to make is I would like for the ranking to not duplicate the ranking in the event of a tie and I would like the ranking to update when I filter the data, but other than those changes it looks like this is working.

Rank X Personal Time = RANKX(ALL('Non Overlapping'[User ID]), CALCULATE(SUM('Non Overlapping'[After Hours] ) , ALLEXCEPT('Non Overlapping', 'Non Overlapping'[User ID])),,DESC,Dense)