Memory issue, Dax Formula is not being processed

[majdkaid22]

HI Community,

not sure if anyone have had similar issue and what is the work around it, rarther than upgrading the PC Ram 

I've got to say, I have 227k rows in that table and my SQL Azure server is in Sydney, while am running it from Dubai

Any thouhgts? 

[MattAllington]

In your biggest table. How many rows, how many columns?

[majdkaid22]

@MattAllington in this particular table I have 227,000 rows and 49 columns.

In other table, I have over than 800,000 rows and around 49 columns as well

[MattAllington]

49 Colums sounds like the problem. In power pivot, you want long narrow tables. You do not want wide tables where ever you can avoid it.

what is in all these columns?  Can you unpivot the data?

[majdkaid22]

@MattAllington am not quite familiar how unpivot would affect my data. I've never did unpivot exrcise. 

Am in FX Trading, and all these columns are related to transactions, exchange prices..ect I've reduced the number of columns to 14, but the DAX formula is still not going through. I won't be able to reduce further as the data would become useless 

[MattAllington · Accepted solution]

Before you move on, you could just try loading less rows or columns and see if that actually fixes the problem. 

Regarding unpivot:

Let's say you have the following 7 columns 

date, stock, buy, sell, high, low, vol

you can load the table into power query (get data). Then select the first 2 columns, right click and select "unpivot other columns. This will then give you 4 columns as follows

date, stock, attribute, value.

the last 4 column headings will now be text in the attribute column

the values from the last 4 columns will be values in the values column. 

Best set thing to do is build a simple test and see how it works. 

You ou then need to change your measures. 

Instead of

=sum(table[vol)]

you would write

=calculate(sum(table[value]),table[attribute]="vol")

[majdkaid22]

@MattAllington Matt, decreasing the number of rows worked perfectly! 

I basically created 2 copies of the same table, one with the values that I want this formula to apply on, and other duplicate table with filtered values don't require the formula (the higher volume of rows are here) 

appreciated mate