PowerPivot - Cube formulas to return member text based on rank

[Willem2904]

I have a table with some focus areas as shown below:

I edit the table in PowerQuery and add an index starting from 1.

Then I loaded the query to the DataModel and added a calculated column for Rank:
=RANKX(FILTER('Qry_Focus_Areas';'Qry_Focus_Areas'[Professional Forum]=EARLIER(Qry_Focus_Areas[Professional Forum]));[Index];;ASC;Dense)
This works fine, as you can see below, it ranks the focus areas grouped by professional forum:

Now I would like to have a cube formula (not pivot table) that returns the focus area if I supply the rank number (e.g. 1, 2, 3,...) and the Professional Forum (e.g. Cables).

Any help is much appreciated!

 

[RoryA]

Please remember:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: PowerPivot - Cube formulas to return member text based on rank
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.