Forum Discussion
Paginated Report issue
Hi,
In my paginated report datasource i am using the semantic model from power bi report. But when i used the same measure in the paginated report the data differs as it is coming correctly for all measures but for Sales, Sales Volume% and YOY%. In the tablix, i am using dimensions in the row group grouping based on the account id and some measures in the column also Month wise sales and semester wise sales in the measures. Here all the data is coming correctly but only the 3 values are not matching. Help me with the solution for this.
6 Replies
v-saisrao-msftCommunity Support
Hi Deeva,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
jubinsoniSkilled Sharer
Hi Deeva,
Since the tablix checks did not fix it, I would look at what is different between the two filter contexts rather than the tablix itself. Two things usually explain "only Sales, Sales Volume % and YOY are off":
1. Filters that exist in the Power BI report but not in the paginated report
The Power BI visual is often affected by report, page or visual level filters (or a slicer on a fact column) that are not part of the measure. The paginated report only sees what is in its own DAX query and parameters. Check the Filters pane in the PBI report, and use Performance analyzer > Copy query on the visual to see the exact DAX it runs.
2. The grain of the paginated dataset
The query designer builds one SUMMARIZECOLUMNS at the row level you selected. Ratio and YOY measures are only correct at the grain they were evaluated at, so semester or total cells built from them in the tablix will not match Power BI. A quick way to compare is to run both queries in DAX query view in Desktop:
// 1) paste the query copied from Performance analyzer (Power BI visual) // 2) paste the paginated dataset query (Dataset properties > Query) // then compare one account side by side: EVALUATE CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Date'[Year], 'Date'[Semester], 'Date'[Month], "Sales", [Sales], "Sales Volume %", [Sales Volume %], "YOY %", [YOY %] ), 'Account'[Account ID] = "<one account>" )If the Power BI query has extra filters (KEEPFILTERS / TREATAS blocks) that the paginated one lacks, that is your difference. If semester values differ but months match, return the semester-level values from the model instead of aggregating months in the tablix, for example a second dataset grouped by Semester, or a dataset that already includes subtotals:
EVALUATE SUMMARIZECOLUMNS ( ROLLUPADDISSUBTOTAL ( 'Date'[Semester], "IsSemesterTotal", 'Date'[Month], "IsMonthTotal" ), 'Account'[Account ID], "Sales", [Sales], "Sales Volume %", [Sales Volume %], "YOY %", [YOY %] )So my overall approach would be:
Copy the Power BI visual query (Performance analyzer) -> Run it and the paginated query side by side for one account -> Different filters? add them as paginated parameters/filters -> Same filters but totals differ? return subtotal rows from DAX instead of Sum() in the tablixIf you can share the measure definitions for Sales Volume % and YOY, it would be easier to tell which of the two it is.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
Hi,
I have followed all the steps mentioned but still no luck the issue is not resolved.- v-saisrao-msft
Hi Deeva,
Have you had a chance to review the solution we shared by maruthisp ? If the issue persists, feel free to reply so we can help further.
Thank you.
The highlighted place i am getting the issue
- maruthisp
Super User
Hi Deeva,
Since you are using the same semantic model and most of the measures match, I don't think the semantic model connection itself is the issue.
I would first check whether the three problem measures are being aggregated again inside the paginated report.
This is particularly important for:
- Sales Volume %
- YOY %
These are non-additive measures and should not normally be summed in the tablix.
For example, if your textbox currently uses:
=Sum(Fields!SalesVolumePct.Value)
or:
=Sum(Fields!YOYPct.Value)
Report Builder may be summing measure results that were already calculated by the Power BI semantic model.
First run the dataset query itself:
Report Data → Dataset Properties → Query → Run Query
Check whether the correct Sales, Sales Volume % and YOY % are returned there for one Account ID and one Month.
If the dataset result is correct but the tablix is wrong, check the textbox expressions.
If the dataset contains only one row for that Account/Month group, use the model value directly, for example:
=First(Fields!SalesVolumePct.Value)
and:
=First(Fields!YOYPct.Value)
rather than summing them.
Also check Sales in the same way because the dataset may contain multiple rows at a lower grain than your Account ID tablix group.
The second thing I would check is the date context used by the dataset. Make sure Month, Semester and Year are coming from the same Date dimension used by the YOY measure in the Power BI model.
If the dataset query itself already returns the wrong value, then compare the generated DAX query with the Power BI visual. Measures using functions such as:
ALLSELECTED() ISINSCOPE() SELECTEDVALUE() DATEADD() SAMEPERIODLASTYEAR()
can return different results when the paginated report supplies a different filter context.
So I would troubleshoot it in this order:
- Run the paginated dataset query.
- Check one Account + Month against Power BI.
- If the dataset is correct, fix the tablix aggregation.
- If the dataset is wrong, check the DAX/filter context and Date dimension used by the dataset.
My first suspicion here would be double aggregation in the tablix or a different Month/Date filter context, rather than an issue with the semantic model itself.
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X
