locked
#ERROR in Calculated Column referencing related table RRS feed

  • General discussion

  • I've got a couple of calculated columns tacked onto my sales data that reference related tables that are linked via the branchcode and transaction reference respectively:

    =if(RELATED(LS[reference1])='Sales Data'[reference1],"Y","N")

    =RELATED('Store Attributes'[FX])

    These work well however I get #ERROR whenever I refresh the sales data only for the formulas to work perfectly well if I re-enter one of them (re-entering one, fixes the other).

    Is there a known solution to this issue?

    Thanks

    Jacob


    Edit......I also seem to be losing the relationship between the sales data and the trading calendar table I use to slice the data by the required time period - although it appears to be there I cannot cut the sales by trading week unless I delete then re-establish the link!!!
    Monday, January 16, 2012 10:37 AM

All replies

  • Hi Jacob,

    Thanks for your question, however I have a little diffcult to understand your issue, could you please supply detail description about it? which is a big help for us supplying further troubleshooting to the issue.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 18, 2012 5:19 AM
  • Thanks Challen, 

    Effectively I have a formula in a calculated column on my sales data which uses the RELATED function to return the corresponding value from the related table. 

    The formula works perfectly fine until I refresh the sales data from the SQL database and once the data has refreshed, the formula which had previously worked now returns #ERROR.

    All I have to do to fix the problem is put a space at the end of the formula, hit enter and it works (doing this for the first formula also fixes the problem with a 2nd formula that has the same issue).

    I also have a hit-and-miss issue with the relationship between the sales and calendar tables where sometimes it doesn't work - I can always see the relationship in the list but when this happens I have to delete the existing relationship and create a new one.

    Thanks again.

    Jacob

    Thursday, January 19, 2012 5:48 AM
  • I experience the same issue, I have not found a work around, so all I do after the refresh is change the =related formula to =1 then change it back to the =related formula.  It appears that this only needs to be done once and not on each tab.
    Sunday, January 22, 2012 9:48 PM
  • Jacob,

    Is this still an issue?

    Thank you!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, November 3, 2013 10:01 PM