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

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:
Ranked.PNG

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!