Power BI Report Builder - wants aggregates

[PetyrBaelish]

I'm developing a report in Power BI Report Builder, that connects to a Power BI dataset.

The Power BI dataset is loosely based on a star schema, but everything I'm using in the paginated report comes from a single table called ACTIVITY. This includes several fields (such as department_name) and some measures - the measures are based on concatenating other fields, for example concatenating all the text values in a field called staff.

Here's an example of the model:

In my report I have created a rectangle with several text boxes inside, and I've placed several fields, such as Department_Name, Team_Name etc. I also have a few text boxes with the measures in, All_Staff for example.

When I try to run the report, I get the following error:

The Value expression for the text box 'Textbox2' refers directly to the field 'Department_Name' without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
----------------------------
The definition of the report '' is invalid.
----------------------------
An error occurred during local report processing.

It seems to be expecting an aggregate, but I don't believe this to be correct as everything is coming from the same table (ACTIVITY) - both the normal/dimension fields like Department_Name and my measures.

I've tried using the aggregate "First" by modifying the expression of the text boxes, for example
=First(Fields!Department_Name.Value), 

=First(Fields!All_Staff.Value), 

I tried applying this to the text boxes containing dimension first (it didn't work), and then to the text boxes containing the measures (it also didn't work).

The report is parameterised, so while I don't want to use any aggregate, I thought this might be a safe one as the report is designed that it will only ever show 1 given activity (hence, there'll only ever be a "first").

So nothing has worked so far - why does Report Builder want something aggregated when everything comes from the same table, and how can I get around this?

[d_gosbell · Accepted solution]

---

@PetyrBaelish wrote:

So nothing has worked so far - why does Report Builder want something aggregated when everything comes from the same table, and how can I get around this?

---

If all you have on your report is a single rectangle then Report Builder is trying to display the result of all the rows in the resultset in this single rectangle and the only way to do this is to apply some sort of aggregate function.

If you want a row in your report for each row in your table then you need to drag a table control onto your report and then put the values you want inside a row in this table object. The report server table control will generate an output row for each row in your dataset. If you want you can put a rectangle inside your table control and layout your text boxes however you want, but the table control needs to be the outer object so that it can generate the repeating rows in the report output. 

[PetyrBaelish]

Thanks @d_gosbell - it was the table control I was missing.

[bradsy]

I think you will need to add the dataset reference to the expression. =First(Fields!All_Staff.Value, "MyDataSetName"),