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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors