PowerPivot DAX formulas #ERROR on refresh

Question

Post reply

PowerPivot DAX formulas #ERROR on refresh

G-Dub

Mr or Mrs. 500

Points: 504
More actions
October 27, 2010 at 8:36 am

#242252

I have a PowerPivot dashboard that I'm trying to prepare for publication to my workgroup. Unfortunately, when I "Refresh All" in PowerPivot, all of the DAX columns change to "#ERROR". I can update the PowerPivot results, but the process is extremely manual, which kind of defeats the purpose of spending this effort on PowerPivot.
Two of the formulas in the cascade of errors are:
=RELATED('FY 2011 Monthly Plan'[MonthHistSales])
=RELATED('FY 2011 Monthly Plan'[MonthHistContMarg])
If I add a space to the formulas after refreshing the tables, they caculate just fine. I've checked, and the source table has updated without any errors.
Other formulas apparently fail because I use composite keys to join the tables (Date & Product Line). The keys all appear to refresh appropriately in the tables. However, all the formulas dependent on those relationships fail. Again, if I go back and force a recalculation of the key columns by adding a space to the formula, the formulas will recalculate properly Sometimes I have to refresh the key column in the destination table, other times in the source table.
Has anyone run into similar issues? Any ideas for solutions or workarounds?
Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

G-Dub Mr or Mrs. 500 Points: 504 More actions · Answer 1

G-Dub

Mr or Mrs. 500

Points: 504

November 1, 2010 at 4:46 pm

#1245132

Anyone? Beuhler?

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 2

I assume that you want to create a calculated column in a PP table.

I tested that procedure in the beta version of PP with problems. It might work now however.

I dont use calculated columns but calculated measures which works fine. I do have some very complicated

formulas.

Why not try to replace your DAX formulas with calculated measures DAX formulas and keep the

PP tables as original from the source or use plain SQL to create the extra column.

Regards

Gosta M

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 3

If I recall correctly someone (might hav been Chris Webb) said in a PowerPivot-demo that it was important that your keys are a single, numeric column. I don't recall the reasons for that (if he gave any), but it might be your problems are related to your composite keys.

Might be worth a shot to try converting to single keys and see if it works better.

Please post back your results or experiences here, as I'm curious about the implications of the composite keys, and don't have a evironment to use Powerpivot at the moment.

Peter Rijs
BI Consultant, The Netherlands

G-Dub Mr or Mrs. 500 Points: 504 More actions · Answer 4

Thanks for the responses. That's an interesting note regarding numeric keys I was using concatenated text keys. I'll keep that in mind for next time.

I worked around the issue by creating an MDX query that did most of the relational calculations before the data is pulled into the PP table. Unfortunately for Peter that means that I don't have any experience to share. If I do find myself confronting this issue again, I'll try converting to numeric keys and post back.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

"If I recall correctly someone (might hav been Chris Webb) said in a PowerPivot-demo that it was important that your keys are a single, numeric column."

My comment is if that is true the benifit of using PP will be very limited.

I do use "notnumeric keys" and numeric keys without problems

I do think it is a good practise to avoid calculated columns in PP tables and instead use SQL to

create the column. For your information I have also built my own API in VB.net omatically to

be able to automatically update the client version of Excel without the need to use Sharpoint Server.

//Gosta M

George W (no relation) Ten Centuries Points: 1339 More actions · Answer 6

Thanks, Gosta. The PP table in question is coming from an SSAS cube, which I think restricts me to using MDX rather than SQL. That's the route I went, so I did wind up following your advice to move the calculations out of PP tables.

Regarding Peter's request, I'm recalling now that I still used concatenated text keys in PP when joining MDX data table to SQL data table, and they seem to work fine now that the keys are not being calculated in PP.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 7

The main advantage with PP is that you don't need to create Olap cubes. But that doesn't remove the

need to create a data warehouse. The ability to import data from different sources is fine but also dangerous as you cannot control the "result" of the combinated data anywhere else but in PP.

My strategy is therefore to create a data warehouse with raw data and if needed views to "export" data

to PP. Then I can create SQL-querys and compare the result with the result in PP (in the run).

I reality the problems are seldom of "technical" nature but bad data quality.

PP is a greate step by Microsoft but MS has not looked carefully enough (or they will) on competitors

like Qlikview.

//Gosta M

George W (no relation) Ten Centuries Points: 1339 More actions · Answer 8

A good strategy, provided the people using PP understand the warehouse data thoroughly. I'm analyzing data from our financial cube, which has our "single version of the truth" numbers, so I'm restricted to the cube data for this exercise.

I also agree with your comments on QlikView - it's great when I want a rich graphic environment for ad hoc analysis. The personal edition is free, and it plays very well with SQL data warehouses. I still use it for non-cube analysis.