Ranking issue

I am fetching data from a SSAS server and want the dataset to return a Ranking value written as a meaure

 

My base table is like this

| fiscal_period 	| Index 	|
|---------------	|-------	|
| 202301        	| 1     	|
| 202302        	| 2     	|
| 202301        	| 3     	|
| 202303        	| 4     	|

 

I want the query to return

| fiscal_period 	| Index 	| Ranking 	|
|---------------	|-------	|---------	|
| 202301        	| 1     	| 1       	|
| 202302        	| 2     	| 2       	|
| 202301        	| 3     	| 1       	|
| 202303        	| 4     	| 3       	|

 

I tried with WINOW function on a large table which generates following error

Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. To run this query, you could simplify the query or its calculations, or reach out to your capacity administrator to see if they can increase the per-query memory limit.

 

//works fine without WINDOW function
DEFINE MEASURE fact[fp]= MAX(fact[fiscal_period])
MEASURE fact[rank_1] = RANKX(ALLSELECTED(fact),[fp],,ASC,Dense)

//generates error -- tested on a small table and works fine
MEASURE fact[rank_2] = RANKX(WINDOW(1,ABS,1,REL,DISTINCT(ALLSELECTED(fact))),[fp],,ASC,Dense)

SUMMARIZECOLUMNS(fact[col1]....fact[coln], "rank",[rank_2])

 

Is there a workaround?

jeffrey_wang CNENFRNL AlexisOlson TomMartens 

 

Thnak you in advance.