Paginated Reports Expressions with parameters

[amgarav]

Hi all 

I'm working on a paginated reports and I have 2 questions regarding the report builder:

1. Are you using a Power BI connection in your work? If so, have you encountered any challenges when using measures from your Power BI model in your paginated report? How did you resolve them? 
2. Currently, I'm working with expressions in my report, and I'm facing an issue with one of them. I'm using the CalendarWeek parameter, and I need to calculate the variation between the selected week and the week five periods before it, as shown in the second image. However, the expression I'm using to calculate the total sum for the previous period is resulting in a blank value.

   =sum(iif(Fields!Year.Value=TODAY.Year AND Fields!CalendarWeek.Value=Parameters!WeeklyVolumeDataCalendarWeek.Value(0)-1,

   Fields!Total_Volume.Value,Nothing))

   

    

[v-yohua-msft · Accepted solution]

Hi, @amgarav 

When using Power BI datasets in paginated reports, it's important to understand that paginated reports don't directly support DAX measures in Power BI models. Instead, you'll need to use SQL queries or expressions to recreate the logic for these measures in a paginated report. This may involve getting the raw data through a query and then applying the necessary calculations in the report itself. For more information about connecting to Power BI datasets and limitations, you can check the following link:

Supported data sources for Power BI paginated reports - Power BI | Microsoft Learn

Regarding your expression issues, it seems likely that there may be spelling mistakes or logical errors in your expressions, especially in the way you handle day and week calculations. The corrected expression might look like this:

=Sum(IIf(Fields!Year.Value = Year(Today()) AND Fields!CalendarWeek.Value = Parameters!WeeklyVolumeDataCalendarWeek.Value - 5, Fields!Total_Volume.Value, Nothing))

Make sure your dataset contains the necessary data for the number of weeks you're trying to compare. If the dataset doesn't have data for the "first five periods" week, the result is indeed empty.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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