The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I have a paginated report which shows the row total as the first line. I have been trying to remove it from the report and no luck yet. Any idea about it?
Solved! Go to Solution.
In the __DS0Core variable you can see the calls to ROLLUPGROUP and ROLLUPISSUBTOTAL functions which are generating this extra total row.
Obviously without your data model I cannot test the query below, but I think I've removed those two functions correctly.
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS ( { "Hours" }, 't_PP11'[UOM] )
VAR __DS0FilterTable2 =
FILTER (
KEEPFILTERS ( VALUES ( 'Date'[weekending] ) ),
AND (
'Date'[weekending] >= DATE ( 2021, 5, 14 ),
'Date'[weekending] < DATE ( 2021, 5, 21 )
)
)
VAR __DS0Core =
SUMMARIZECOLUMNS (
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_Project'[Project Engineer],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending],
__DS0FilterTable,
__DS0FilterTable2,
"SumActual_MH", CALCULATE ( SUM ( 't_PP11'[Actual MH] ) )
)
VAR __DS0PrimaryWindowed =
TOPN (
502,
__DS0Core,
[IsGrandTotalRowTotal], 0,
't_Project'[ Project Engineer], 0,
't_PP11'[Employee or Supplier], 1,
't_class'[Adjusted Exp. Cat. Name], 1,
't_PP11'[Booking Code], 1,
't_Project'[Job Code], 1,
't_Project'[Job Description], 1,
'Date'[weekending], 1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
't_Project'[ Project Engineer] DESC,
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending]
Thank you, this worked!
In the __DS0Core variable you can see the calls to ROLLUPGROUP and ROLLUPISSUBTOTAL functions which are generating this extra total row.
Obviously without your data model I cannot test the query below, but I think I've removed those two functions correctly.
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS ( { "Hours" }, 't_PP11'[UOM] )
VAR __DS0FilterTable2 =
FILTER (
KEEPFILTERS ( VALUES ( 'Date'[weekending] ) ),
AND (
'Date'[weekending] >= DATE ( 2021, 5, 14 ),
'Date'[weekending] < DATE ( 2021, 5, 21 )
)
)
VAR __DS0Core =
SUMMARIZECOLUMNS (
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_Project'[Project Engineer],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending],
__DS0FilterTable,
__DS0FilterTable2,
"SumActual_MH", CALCULATE ( SUM ( 't_PP11'[Actual MH] ) )
)
VAR __DS0PrimaryWindowed =
TOPN (
502,
__DS0Core,
[IsGrandTotalRowTotal], 0,
't_Project'[ Project Engineer], 0,
't_PP11'[Employee or Supplier], 1,
't_class'[Adjusted Exp. Cat. Name], 1,
't_PP11'[Booking Code], 1,
't_Project'[Job Code], 1,
't_Project'[Job Description], 1,
'Date'[weekending], 1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
't_Project'[ Project Engineer] DESC,
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending]
That looks like it's probably being generated by your query. So either remove the code that is generating the total (or show us the query so we can tell you how to do that). Or apply a filter to your table to exclude rows with a blank in the Supplier column. Fixing the query is probably the better option as it means the server will have to do less work to return the results
This is thequery:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"Hours"}, 't_PP11'[UOM])
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('Date'[weekending])),
AND('Date'[weekending] >= DATE(2021, 5, 14), 'Date'[weekending] < DATE(2021, 5, 21))
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_Project'[Project Engineer],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
"SumActual_MH", CALCULATE(SUM('t_PP11'[Actual MH]))
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
't_Project'[ Project Engineer],
0,
't_PP11'[Employee or Supplier],
1,
't_class'[Adjusted Exp. Cat. Name],
1,
't_PP11'[Booking Code],
1,
't_Project'[Job Code],
1,
't_Project'[Job Description],
1,
'Date'[weekending],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
't_Project'[ Project Engineer] DESC,
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending]