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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
amgarav
Frequent Visitor

Paginated Reports Expressions with parameters

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))

     

    amgarav_1-1718981933724.jpeg

     Screenshot 2024-06-21 170116.png
     
     
     
1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-yohua-msft
Community Support
Community Support

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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