Cube formula in excel

[GustavBA]

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:

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

 

[macfuller]

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.

 

[GustavBA]

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.