Power BI DAX query to Paginated report

[GlitterL]

Hey All!!

I created Matrices in Power BI and I tried to copy the DAX query from Performance analyzer, but it does not display all of my data on the Dataset - it gives me "Minname", Do I have to drag all my functions into this query?

this is my DAX query below;

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month],
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo],
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('Surface water (2)'[Text After Delimiter], 'Surface water (2)'[Custom]), "IsGrandTotalColumnTotal"
),
"SumBH_19", CALCULATE(SUM('Surface water (2)'[BH 19]))
)

VAR __DS0CoreOnlyOutputTotals =
SELECTCOLUMNS(
KEEPFILTERS(FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalColumnTotal] = FALSE)),
"'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month]", 'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month],
"'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo]", 'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo],
"'Surface water (2)'[Text After Delimiter]", 'Surface water (2)'[Text After Delimiter],
"'Surface water (2)'[Custom]", 'Surface water (2)'[Custom],
"SumBH_19", [SumBH_19]
)

VAR __DS0CoreTableByDM0 =
SELECTCOLUMNS(
KEEPFILTERS(FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalColumnTotal] = TRUE)),
"'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month]", 'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month],
"'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo]", 'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo],
"SortBy_DM0_0", [SumBH_19]
)

VAR __DS0PrimaryWithSortColumns =
NATURALLEFTOUTERJOIN(
SUMMARIZE(
__DS0Core,
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month],
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo]
),
__DS0CoreTableByDM0
)

VAR __DS0PrimaryWindowed =
TOPN(
101,
__DS0PrimaryWithSortColumns,
[SortBy_DM0_0],
1,
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo],
1,
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month],
1
)

VAR __DS0SecondaryBase =
SUMMARIZE(
__DS0CoreOnlyOutputTotals,
'Surface water (2)'[Text After Delimiter],
'Surface water (2)'[Custom]
)

VAR __DS0Secondary =
TOPN(
102,
__DS0SecondaryBase,
'Surface water (2)'[Custom],
1,
'Surface water (2)'[Text After Delimiter],
1
)

VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0CoreOnlyOutputTotals,
"ColumnIndex",
__DS0Secondary,
'Surface water (2)'[Custom],
ASC,
'Surface water (2)'[Text After Delimiter],
ASC
)
)

EVALUATE
ROW(
"MinName_2", CALCULATE(MIN('Surface water (2)'[Name 2]))
)

EVALUATE
__DS0Secondary

ORDER BY
'Surface water (2)'[Custom], 'Surface water (2)'[Text After Delimiter]

EVALUATE
__DS0BodyLimited

ORDER BY
[SortBy_DM0_0],
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[MonthNo],
'LocalDateTable_00624f73-41fe-4809-bb04-3910ff3999b6'[Month],
[ColumnIndex]

It is suppose to look something similar to this - 

I have a matrix for each category that also needs to display on seperate pages in my paginated report,

Please assist if you can 😊

[liuqi_pbi · Accepted solution]

Hi @GlitterL 

I found a thread that may be helpful: Solved: Using Paginated Report Builder and Existing DAX fo... - Microsoft Power BI Community

In addition, the following article and video about creating a matrix in a paginated report may help:

Create a matrix in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microso...

Create Table or Matrix Report using a Wizard in Paginated Reports in Power BI (5/20) - YouTube