locked
TableA.FieldA value cannot be determined if adding as calculated field to TableB even though good relationship exists. RRS feed

  • Question

  • Assume table "Sales" contains a foreign key, ItemID and a field, NonStandardUnits. And assume table "Standards" contains a Primary key of ItemID and a field StandardUnits, such that a relationship is established between Sales[ItemID] and Standards[ItemID]. How would you implement the following business rule: if NonStandardUnits is not empty, then fill Calc_Units (calculated field) with value in NonStandardUnits, otherwise fill will value in StandardUnits?
    I have been unable to create a calculated field in Sales that refers to any field in Standards.

    So, for example the DAX expression =Standards[StandardUnits] as a calculated field in Sales returns the error:
    "The value for column 'NonStdUnits' in table 'Standards' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation–such as sum, average, or count–on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified." 

    I have a small sample xlsx file which reproduces this condition. 


    • Edited by D Bliss Friday, June 3, 2016 9:05 PM
    Friday, June 3, 2016 4:34 PM

Answers

  • Hi D Bliss,

    Since you're trying to create a calculated field (also known as a measure), you'll need to perform an aggregation function on the referenced columns. If I've understood your scenario correctly, we need to ensure that the conditional check (i.e. whether to use NonStandardUnits or StandardUnits) is evaluated at the row level before summing the result.


    Units:=
    SUMX(
      Sales, 
      IF(
        ISBLANK(Sales[NonStandardUnits]), 
        RELATED(Standards[StandardUnits]), 
        Sales[NonStandardUnits]
      )
    )

    If this doesn't behave as you're expecting, it would be helpful if you could walk through a simple example using a very small test dataset to clarify the expected behaviour. That way another member of the forum or myself can provide further solutions.

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, June 3, 2016 9:57 PM