Calculated column error at first refresh

[PhilipVerspreeuwen]

Hello all,

After searching for a solution for a few days now, I can't settle with my current workaround... All help is therefore appreciated.

Situation: I have an excel 2013 (pro) worksheet coupled to a data warehouse (SQL2005). In this excel, I created a datamodel with different calculated fields and columns. So far so good, everything works with no error as long as I am in excel.

Problem: When I open my excel and it loads the data for the first time, it errors on 2 calculated columns (different times): The query referenced calculated column 'X' which does not hold any data because evaluation of one of the rows caused an error.

Dirty solution 1: When I discard these errors and refresh my data once my excel is open, no errors given and all calculated columns are ok. This is not a 'clean' solution... I would prefer that the calculated columns are ok when opening the excel and loading the data the first time.

Details:
The following calculated column is giving the error when opening the excel:
=IF([nummer]=MINX(FILTER(cynheadtel;[hoofdnummer]=EARLIER([Hoofdnummer]));[nummer]);RELATED(cynform[geleverdv])*RELATED(BI_Artikels[cu_per_vo]);0)
All rows in the column have value #ERROR

Dirty solution 2: I have 'wrapped' the formula in an IFERROR(formula;0), this 'hides' the error messages so I just need to refresh after the intial load. Nevertheless, It would still be better without an aditional refresh. Instead of #ERROR, all rows now have the value '0'

I just can't figure out why a refresh solves my problem... If I need to give any additional input, just let me know. But at this moment I hope I can just rephrase the formula so it works without reload.

 

[PhilipVerspreeuwen]

I have investigated the problem and discovered some things that could be usefull to solve the problem.

The formulas only work when refreshing the table containing the calculated column in the data model view. If I use "refresh all" in the workbook or data model, result is the same as when opening the workbook. Also 'recalculate' doesn't solve the problem.

Reading a lot of posts and Microsoft help, makes me think that the problem is a reference/relation that is not there while refreshing the complete model, but is made when I only refresh that one table.

Is there any way to 'force' an automatic refresh of that one table after loading the complete model? (this would be dirty solution n°3...). Better would even be to update all tables (7 in total) and afterwards updating the one needing relations (8th table) This would avoid double loading.

 

[PhilipVerspreeuwen]

Problem solved = Dirty solution N°4...

As I figured out something was wrong with the relations, I tried a basic solution:
Instead of nesting 'related' values in my 'calculated column', I added some columns with the related data to the table and referred to those columns in the 'calculated column' formula.

This solves the problem!!!

Yet again, Excel forces me to use workarounds rather than using my logical mind... This time the underlying problem is a cache refresh problem of the data/relations...

Result:
- Worksheet loads first time right
- size increase of 0,6 MB
- Load time doesn't seem to be affected (it might be slightly, but not visible)

 

[scottsen]

Interesting. I had nothing to add here... was a such a weird problem. But now that you got so far, can you tell me the before and after of one of your calculated columns that impacted this?

 

[PhilipVerspreeuwen]

before it was like this:
=IF([nummer]=MINX(FILTER(cynheadtel;[hoofdnummer]=EARLIER([Hoofdnummer]));[nummer]);RELATED(cynform[geleverdv])*RELATED(BI_Artikels[cu_per_vo]);0)

After it was like this:
=IF([nummer]=MINX(FILTER(cynheadtel;[hoofdnummer]=EARLIER([Hoofdnummer]));[nummer]);[geleverdv]*[cu_per_vo];0)

To make this formula possible I added two calculated columns to the table:
[geleverdv] = RELATED(cynform[geleverdv])
[cu_per_vo] = RELATED(BI_Artikels[cu_per_vo])

 

[scottsen]

Great, now I am going to have to stop yelling at me for using those style of simple "related" calculated columns That's really weird - I have never seen something like this.

 

[TonyB123]

Thanks Phillip. I too was sctatching my head when i saw this error message. Would never have guessed to remove "related" from the equation! I owe you a beer