I have the following two "test purpose" tables in Excel 2013: 

Table1: 

Product Sell Rate

A 10

B 20

Table2:

Product Sell Qty

A 1

B 2

A relationship is established on the Product columns of both the tables. 

Now in the PowerPivot, I try to add a column in the Table2 with the formula: =[Sale Qty]*Table1[Sale Rate]. 

However, this does not work. An error is displayed: 

"The value for column 'Sale Rate' in table 'Table1' 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 tried several variations, but I could not resolve this error. 

Can someone please help me here. 

TIA

Hi TIA,

According to your description, this error is due to the fact that you have a formula which references a cell from another table and there is no way to determine the current context for that row. In order to understand the current context and take advantage of relationship we need to use a DAX function Related function.

You need to modify the formula, DAX formula needs RELATED(), please refer to the following formula.

=[Sale Qty]*RELATED(Table1[Sale Rate])

After you modify this you should see the correct result. I suggest you to read this article and get more information:

http://technet.microsoft.com/en-us/library/hh213101.aspx

Hope its helpful.

Regards,

Hi TIA,

You can get this error message in DAX when you are adding new calculated column or when you are adding new calculated measure.

If you are getting this error message when you are adding new calculated column, that means that you are referencing column "ColumnName1" from the table "Table1", but table where you adding this DAX calculated column is not related to the table "Table1". To fix this you need to create relationship between your table where you adding calculation and the table "Table1".

If you are getting this error when you are adding new calculated measure (in the PivotTable interface), that means that you are using Table1[ColumnName1] column in your DAX formula, but you did not specify what type of aggregation you want to use in formula context. To fix this you should specify column aggregation function, for example: SUM(Table1[ColumnName1]).

So you need to modify the formula, DAX formula needs RELATED(), please refer to the following formula.

=[Sale Qty]*RELATED(Table1[Sale Rate])

After you modify this you should see the correct result. I suggest you to read this article and get more information:

http://technet.microsoft.com/en-us/library/hh213101.aspx

Hope its helpful.

Regards,

• Edited by guGuuuMy Monday, May 04, 2015 9:07 AM

Hi TIA,

You can get this error message in DAX when you are adding new calculated column or when you are adding new calculated measure.

If you are getting this error message when you are adding new calculated column, that means that you are referencing column "ColumnName1" from the table "Table1", but table where you adding this DAX calculated column is not related to the table "Table1". To fix this you need to create relationship between your table where you adding calculation and the table "Table1".

If you are getting this error when you are adding new calculated measure (in the PivotTable interface), that means that you are using Table1[ColumnName1] column in your DAX formula, but you did not specify what type of aggregation you want to use in formula context. To fix this you should specify column aggregation function, for example: SUM(Table1[ColumnName1]).

So you need to modify the formula, DAX formula needs RELATED(), please refer to the following formula.

=[Sale Qty]*RELATED(Table1[Sale Rate])

After you modify this you should see the correct result. I suggest you to read this article and get more information:

http://technet.microsoft.com/en-us/library/hh213101.aspx

Hope its helpful.

Regards,

• Edited by guGuuuMy Monday, May 04, 2015 9:07 AM
• Marked as answer by Sau2001 16 hours 44 minutes ago

Hi TIA,

You can get this error message in DAX when you are adding new calculated column or when you are adding new calculated measure.

If you are getting this error message when you are adding new calculated column, that means that you are referencing column "ColumnName1" from the table "Table1", but table where you adding this DAX calculated column is not related to the table "Table1". To fix this you need to create relationship between your table where you adding calculation and the table "Table1".

If you are getting this error when you are adding new calculated measure (in the PivotTable interface), that means that you are using Table1[ColumnName1] column in your DAX formula, but you did not specify what type of aggregation you want to use in formula context. To fix this you should specify column aggregation function, for example: SUM(Table1[ColumnName1]).

So you need to modify the formula, DAX formula needs RELATED(), please refer to the following formula.

=[Sale Qty]*RELATED(Table1[Sale Rate])

After you modify this you should see the correct result. I suggest you to read this article and get more information:

http://technet.microsoft.com/en-us/library/hh213101.aspx

Hope its helpful.

Regards,

• Edited by guGuuuMy Monday, May 04, 2015 9:07 AM
• Marked as answer by Sau2001 Saturday, May 09, 2015 2:43 PM

Hi,

Thanks for your suggestion. However, even after using the RELATED(), it did not work. Then I used a simpler formula, just copy SaleRate from Table1 into a calculated column in Table2, using =related(Table1[Sale Rate]). However, even this did not work. The error displayed this time is:

"The column 'Table1[Sale Rate]' either doesn't exist or doesn't have a relationship to any table available in the current context."

All the time, both the tables are related through their Product columns. 

Could  you please help further. 

Regards,

Sau2001

Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
Depending on the arrangement, type, repetition and duplication of data,
a unique solution might be required for each.
Here is my idiosyncratic example.
http://www.mediafire.com/view/csu1ylpm8lkvr74/05_06_15.xlsx
Share your sanitized file if this is not enough of a nudge.

Hi Sau2001,

Please refer to the sample of "Herbert Seidenberg", and check if it works for you.

And I recommend posting your issue to PowerPivot forum, I think that forum can offer more professional answers about PowerPivot. 

The forum of PowerPivot:

https://social.technet.microsoft.com/Forums/office/en-US/home?forum=sqlkjpowerpivotforexcel

Regards,

Hi guGuuuMy,

I could finally make it work using your suggestion to use the RELATED() function.  Can't figure it out why it was not working the very next day when I tried it out. But its working now.

I found that I can create a Calculated Column in the table having sell info such as Qty. But I could not create it in the table which contains reference info such as Rate. This seems logical somehow, but I guess I need to understand it better. But its clear that the RELATED() works. Thanks a lot for the same. 

Regards,

Sau2001