cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
humid
Frequent Visitor

Paginated Report not distributing Count values correctly

Hello,

 

I've just started to work a bit with Report Builder with our Power BI Report Server and although I've done some progress, I'm right now stuck with a requeriment which is so simple in Power Bi, but seems a bit more complex in a paginated report.

 

First I've created a KPI which is a simple COUNT with some filter expresions:

 

=CountDistinct ( IIF
( Fields!Diff.Value > 0 And Fields!Action_Phase.Value <> "Complete" And Fields!Action_Phase.Value <> "Void",Fields!Number.Value,Nothing),"DataSet1")

 

If I put this as an expression in a TextBox, works perfectly and gives me the expected result, say 150

 

However, now I have to create a table like: City ; Count, but when I create this simple table and set the expresion also in the table, I obtain the following result:

 

City      Count

A           150

B           150

C           150

 

When the expected result would be:

 

City      Count

A           100

B           25

C           25

 

It seems the count is not being aggregated correctly.

 

I've also tried to create the count as a calculated field in the DataSet, and use this field in the table instead, but is not allowing me as it gives me the following error when I execute the report:

 

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

 

Any suggestions?

 

Thanks in advance!

 

 

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

Hello,

I've just started to work a bit with Report Builder with our Power BI Report Server and although I've done some progress, I'm right now stuck with a requeriment which is so simple in Power Bi, but seems a bit more complex in a paginated report.

First I've created a KPI which is a simple COUNT with some filter expresions:

=CountDistinct ( IIF
( Fields! Diff.Value > 0 And Fields! Action_Phase.Value <> "Complete" And Fields! Action_Phase.Value <> "Void",Fields! Number.Value,Nothing),"DataSet1")

If I put this as an expression in a TextBox, works perfectly and gives me the expected result, say 150

However, now I have to create a Matrix like: City ; Count, but when I create this simple table and set the expresion also in the table, I obtain the following result:

City Count

A 150

B 150

C 150

When the expected result would be:

City Count

A 100

B 25

C 25

It seems the count is not being aggregated correctly.

I've also tried to create the count as a calculated field in the DataSet, and use this field in the table instead, but is not allowing me as it gives me the following error when I execute the report:

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

Any suggestions?

Thanks in advance!

v-binbinyu-msft
Community Support
Community Support

Hi @humid ,

For create paginated report in report builder, if you create matrix visual, you can set the aggregation type by select during design time.

For more details, you can read related document: Tutorial: Creating a Matrix Report (Report Builder) - SQL Server Reporting Services (SSRS) | Microso...

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, when I create the Matrix i receive the following error:

 

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

 

Metric_1 = CountDistinct ( IIF
( Fields!Diff.Value > 0 And Fields!Action_Phase.Value <> "Complete" And Fields!Action_Phase.Value <> "Void",Fields!Number.Value,Nothing),"DataSet1")

 

Any help?

Hello binbinyu, what is explained in the tutorial is quite basic and simple. In my case, the count measure I want to put in the table, is a measure created with some filter conditions.

 

In Power BI Report, when I create the count measure and I put it on a table with other dimensions, it is correctly aggregated. However, I'm not sure how to replicate the same behavior in the Report Builder, as when I try the same, I receive the aggregation error

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.