Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.