Paginated Reports: The aggregate function "Aggregate" cannot be used in report that contains filters

[c37582]

Hi guys,

First of all apologies if I'm not posting this in the right forum - I couldn't see any board for Power BI Report Builder.

Wondering if anyone out there is familiar with using the "Aggregate" function in their expressions to retrieve server level aggregates of measures from Power BI datasets?

Basically, I have a measure that is a percentage, "Renewal Rate". This is defined in my underlying Power BI dataset to be the result of one measure divided by another measure, "the number of leases that renewed" divided by the "number of leases that expired".

I have brought this % measure through into my paginated report dataset calculated at the month level:

Hence, I am easily able to retrieve the percentage value for each month and display that in my tablix using the expression Sum(Renewal_Rate):

However, I would ideally like to display an overall total percentage for the whole year as well. Obviously, this cannot be calculated in the same way, I need Sum(number of leases that renewed)/Sum(number of leases that expired), for the whole year, rather than just a sum of the percentages for each month. Now I could just bring those two underlying measures into my dataset and do exactly that calculation, but my understanding from the Paginated Reports in a Day Youtube video series is that using the Aggregate function I should be able to have Power BI Report Builder fetch that total result without needing to bring in these two measures?

My query is already in MDX, so following the steps they do in the video I write in the Aggregate(Renewal_Rate) expression into my new "Total" column, and it updates the query correctly with the result I want:

However for some reason when I go to render the report it fails saying, the aggregate function "Aggregate" cannot be used in a report that contains filters. I don't understand what filters it's referring to. I know it can't be filters on the dataset query because they used filters in the Paginated Reports in a Day video as well and it worked fine for them. Has anyone had any experience with resolving this issue/know what it could be that I'm doing wrong?

[Anonymous]

Hi @c37582 ,

The error message suggests that there might be filters applied at some level in your report which are conflicting with the use of the "Aggregate" function. This could be filters applied directly on the dataset, on the tablix, or even as report parameters that influence the dataset query.

Ensure that the expression used for the "Aggregate" function is not combined with other functions that are not supported.

More details: Aggregate function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Micr...

Best Regards
Community Support Team _ Rongtie

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

[c37582]

Hi @Anonymous ,

Thanks for your reply. I don't have any filters on the tablix or report parameters. There are filters on the dataset, but as I said in my post there are filters on the dataset that is used in the "Paginated Reports in a Day" video as well and the function works fine. In any case, when I remove the filters from the dataset I still get the same error: