Hi,<o:p></o:p>

I am having PWA data in MS excel in different sheets like Project, Task,Assignment and Resource. so i want to calculate metrics using few columns from different sheets.<o:p></o:p>

Is it possible to add multiple columns from different sheets in single expression in power pivot?<o:p></o:p>

please help me in this.<o:p></o:p>

Thanks in Advance<o:p></o:p>

Sarath.<o:p></o:p>

• Edited by Michael AmadiModerator Wednesday, July 23, 2014 7:52 PM Minor title amendment

Hi Sarath,

Yes, it is possible to use data from different sheets in power pivot in a single expressions.

What you probably want to do is to import those sheets that are used a the data source as linked tables into power pivot - it is explained here.

Each sheet will correspond to a single table in Power Pivot. Depending on your data model you may add relationships between your tables. This actually entirely depends on your specific situation - the same is valid for the definition of your metrics.

I would recommend reading about Power Pivot - powerpivotpro.com is a very good resource.

Regards,

Julian 

Hi Julian,

Thanks for your reply. i will check your references and get back to you.

Regards,

Sarath,

You're getting this error because of the ending portion of your IF() function. The error message in this case is very helpful:

The value for column 'AssignmentBaselineWork' in table 'AssignmentBaselines' cannot be determined in the current context. 

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.

One of these two sections is the cause of your problem.

In the first case, you either do not have relationships appropriately defined between the tables and the Power Pivot engine cannot figure out which value you want.

In the second case, there are multiple possible values of 'AssignmentBaselines'[AssignmentBaselineWork] and you need to wrap that reference in an aggregation function. 

If you could provide us a data sample including an example of expected results, that would be most helpful.

Hi Julian,

I am trying to write below expression but i am getting below error. please help me in this.

=IF(Tasks[TaskActualFinishDate]<="22-07-2012",0,AssignmentBaselines[AssignmentBaselineWork])

ERROR:

Calculation error in measure 'AssignmentBaselines'[Calculated field 1]: The value for column 'AssignmentBaselineWork' in table 'AssignmentBaselines' 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.

Hi Julian,

Here i am providing the sample data. calculted value's are expected results and i defined

TaskActulaFinishDate	Statudate	AssinmentBaselinework	Calculated Value
04-08-2013	02-02-2014	58	58
03-03-2014	02-02-2014	65	0
08-09-2013	02-02-2014	23	23
01-01-2014	02-02-2014	34	34
02-03-2014	02-02-2014	9	0
23-03-2014	02-02-2014	26	0

Plese help me in this

Thanks,

Sarath.

Expression for above calculation

=If(taskActualFinishDate<=StatusDate,AssignmantBaseLinework,0)