Paginated Report - Add Calculated Column - Lookup

[Syndicate_Admin]

Good afternoon

I've been using Power BI for a few days and even less than discovered the paginated reports.

The truth is that I am quite impressed with everything that this environment offers, however, I find myself with a problem that is not solved.

I indicate that my source is a SQL Server database, which I have connected to the report builder and I have added the corresponding dataset to extract all the information I need to the report, I also have two parameters that I use, which work without problem to perform the filters.

The issue is that I have a dataset "Scopes" and I need to add a column with information from a different dataset "SubcontratasTraspasos", however I do not get it.

I tried to get it with the following code as both "Calculated field" and "Query field":

=Lookup(Fields! IdItem.Value, Fields! Id.Value, Fields! QtyDestiny,"SubcontratasTraspasos")

With Query field, although the report runs, it does not show the information I want, and with Calculated field I get the following error:

The expression used for the calculated field 'Traspasos' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

Can someone tell me what I'm doing wrong and how to fix it?

Thank you very much in advance

[v-xiaosun-msft]

Hi @Syndicate_Admin ,

According to your description, here is my suggestion.

As a paginated report author, you can implement report filtering in two ways. You can map report parameters to:

1, Dataset filters, in which case report parameter values are used to filter the data that the dataset has retrieved.

2, Dataset parameters, in which case report parameter values are injected into native queries sent to the data source.

When you use lookup function, please mind these limitations.

1, The result of Lookup is evaluated after all filter expressions are applied.

2, Source, target, or result expressions cannot contain references to lookup functions.

3, The source and destination expressions must evaluate results on the same data type. 

4, Source, target, and result expressions cannot contain references to report or group variables.

Please refer to these documents which will be helpful to you.

Lookup functions in paginated reports - Microsoft Report Builder & Power BI Report Builder | Microso...

Data retrieval guidelines for paginated reports - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hello @Syndicate_Admin ,

Based on your answer, I understand that perhaps I have not explained myself correctly.

The filters within the paginated report work perfectly for me, only the thing is that I want to add a new column in a table whose origin is the result of a search in another table with a matching field.

Scope: Table shown where I want to add the new column

Scopes[IdItem]: Search field

SubcontractorsTransfer: Table to perform the search

SubcontractorsTraspasos[Id]: Coincident field

SubcontratasTraspasos[QtyDestiny]: Numerical value to extract for column "Transfers".

Thanks a lot.

Best regards.

[rhinestonelife]

Hi, did you leave off the .Value on the third variable in your equation? 

Fields!QtyDestiny.Value

Should it be like this? I am learning too.