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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rajivraina
Helper II
Helper II

Help streamline formula so I don't get "not enough memory error"

Hi all,

 

I'm trying to calculate the percent rank of countries by different macro economic indicators. To calculate the percent rank of something you need the rank of each datapoint within the dataset and the max of the dataset. I have created  Rankx and Maxx measure to achieve this. I had to add NOT(ISBLANK to make sure it doesn't count entries that don't have data, and I include a filter by Data and Groupings.

 

Indicator Rankx = IF(FIRSTNONBLANK(Categories[Category],1)="Balance of Trade"&&NOT(ISBLANK([Value])),RANKX(FILTER(ALL(GNI),[Value<>0&&[Value]=CALCULATE([Value],VALUES('Date'[Date]))<>BLANK()&&[Grouping_column]=MAX(GNI[Grouping_column])),[Value],,DESC,Skip)
 
Indicator MAXX Rank = MAXX( FILTER(ALL(GNI),[Value]<>0&&[Value]=CALCULATE([Value],VALUES('Date'[Date]))<>BLANK()&&[Grouping_column]=MAX(GNI[Grouping_column])),[Indicator Rankx])
 These two measures take a few seconds longer than most measures to load, but do work fine. Finally I have a measuer that calculates the percent rank using the prior two measures, and this is the measure that should work but gives me the memory error:
 
Indicator Pct Rank = IF(NOT(ISBLANK([Value])),([Indicator MAXX Rank]-[Indicator Rankx])/[Indicator MAXX Rank],BLANK())
It is simply the (Max - Rank)/Max, which I feel like shouldn't be that hard to calculate? I'm assuming it has something to do with the prior two measures being X functions and having to recalculate for each row in the table OR my use of the VALUES function which also recalculates a table each time I believe?..is there any way around this or to optimize my formulas? 
 
Let me know if my explanation is unclear or if you need any more information.
 
Thanks in advance for any help you are able to provide.
 
-Raj
2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

Hi @rajivraina ,

>>It is simply the (Max - Rank)/Max, which I feel like shouldn't be that hard to calculate?

Yes, it is simply the (Max - Rank)/Max, but when this measure runs, it will also run the Indicator Rankx and Indicator MAXX Rank measure together.

Could you please tell us have you used the 64-bit PowerBI desktop, besides, how many data are you using?

Best Regards,

Teige

 


@TeigeGao wrote:

Hi @rajivraina ,

>>It is simply the (Max - Rank)/Max, which I feel like shouldn't be that hard to calculate?

Yes, it is simply the (Max - Rank)/Max, but when this measure runs, it will also run the Indicator Rankx and Indicator MAXX Rank measure together.

Could you please tell us have you used the 64-bit PowerBI desktop, besides, how many data are you using?

Best Regards,

Teige


Hi @TeigeGao ,

 

I am using the 64 bit version, and have about 300,000 rows of data. Total file size is about 13 MBs.

 

Let me know if you need any more info.

 

Thanks,

Raj

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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