Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
smpa01
Super User
Super User

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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
jeffrey_wang
Power BI Team
Power BI Team

Stay with RankX. We'll release a new ranking function that's based on the window function architecture.

View solution in original post

2 REPLIES 2
jeffrey_wang
Power BI Team
Power BI Team

Stay with RankX. We'll release a new ranking function that's based on the window function architecture.

@jeffrey_wang  Thanks !!! looking forward to it

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.