Cube formula in excel

GustavBA

New Member
Joined
Mar 14, 2008
Messages
22
Hi all

I have a challenge in excel that I cant seem to solve.

I built i fairly straight forward financial reporting model using power pivot. Some data are fetched from Dynamics NAV, some from within excel. All data is returned to the data model using queries.

I have set up a report in excel using cube formulas, but once in a while for no obvious reason some of the cubemember formulas suddenly return #NA in excel.

Ex. =CUBEMEMBER("ThisWorkbookDataModel";"[Measures].[Total_Amount_USD]") returns #NA in excel.

The funny part is that if I make a copy of that measure in the data model that measure seem to work...at least for a while.

The DAX is:

Code:
Total_Amount_USD:=-CALCULATE (
    SUMX (
        'GL Entries';
        'GL Entries'[Amount]
            / CALCULATE (
                VALUES ( CurrencyRates[Rate] );
                FILTER (
                    CurrencyRates;
                    CurrencyRates[Period] = 'GL Entries'[Period]
                        && CurrencyRates[Currency code] = 'GL Entries'[Currency]                  
                )
            )
    )
)

I have had this problem a year ago as well, without finding a solution.
PLEASE help this is really troublesome

Br Michael
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

macfuller

Active Member
Joined
Apr 30, 2014
Messages
294
Office Version
  1. 365
Platform
  1. Windows
I've seen this too, though the issue like yours is intermittent. Your example just shows the measure in the CUBE statement and no other filtering - if you have a slicer you'll need to include the slicer in the statement that matches the FILTERs in your CALCULATE for Period and Currency Code.

I have large datasets so I have to run 64-bit which has been more flaky than the 32-bit. Because the data takes so long to load I tend to lump all my measures into a single model - over 200 with at least 4 fact tables of 2+ million rows each - which I'm sure isn't good practice, but that's the way it goes. Simple measures such as LASTDATE to indicate the latest data available can show as an NA when used in a CUBE formula. I love CUBE functions for the simple writing but they can be temperamental. The more you have, the longer the "Refreshing Background Query..." message stays in the status bar and the more likely NA shows up in at least one.


  1. Are you running off a corporate computer with policies set by administrators, and are you accessing data from shared drives? This may be interfering with the ability to complete a query. This may also result in a lag for the updates you receive on Excel, and thus bugs may appear later and last longer than on a personal machine.
  2. Are you running a large data model that takes a long time to update? In some cases the NA indicates the model is unable to complete the query. Rebooting may help, otherwise slimming the amount of data and removing some measures may help.
  3. The data may not be "clean" if you are refreshing from external sources such as an ERP system and using Power Query. Errors, blanks, or unreadable characters like copyright (c) or registration (r) might be in your fields. Use PQ to clean the troublesome columns.

Worst case, I've had to rebuild the spreadsheet from scratch - not an attractive option but sometimes it's the only solution. Whenever it happens be sure to send a frown to MSFT and maybe they'll finally figure it out.
 
Last edited:

GustavBA

New Member
Joined
Mar 14, 2008
Messages
22
Thx for your reply McFuller.

I work on a citrix excel without adm rights. Its a fairly simpel model with less than 1 mio transactions.

I was now able to discover what triggered the error and I have a work around.

I set up a financial report in excel using cube formulas. In the excel model I mapped the general ledger accounts with mapping categories from a Mapping dimenson table maintained within excel. One of the mapping categories (EST. BONUS) had to be renamed to "BONUS PAID, CURRENT YEAR" so I did a search and replace to rename all occurences and the refreshed the whole model...and this triggers the error.
However if I simply add the new mapping category "BONUS PAID, CURRENT YEAR" in the dimension table and keep the old value the error is not triggered.

I have no clue why this is so.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top