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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.