How to remove TOPN function for Paginated Report?

[Unglaublichusa]

Hello,

I am following a Tutorial (https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-shared-datasets) and it states "If your query includes the TOPN function, delete it from your query". However, when I try, I must be deleting too much. How do I know EXACTLY which text to delete?

Thanks, cheers!

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Date'[Month])),
NOT('Date'[Month] IN {"October",
"November",
"December"})
)

VAR __DS0FilterTable2 =
TREATAS({"Domestic"}, 'Customers'[Sales Division])

VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('SSM'[SalesPerson Territory])),
NOT('SSM'[SalesPerson Territory] IN {""})
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Focus Products'[Item Category], "IsGrandTotalRowTotal",
'Focus Products'[Item Family], "IsDM1Total",
'Focus Products'[Product Code-Desc], "IsDM2Total"
),
'Date'[Year],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"SumQuantity", CALCULATE(SUM('Invoice Details'[Quantity]))
)

VAR __DS0PrimaryWindowed =
TOPN(
102,
SUMMARIZE(
__DS0Core,
'Focus Products'[Item Category],
[IsGrandTotalRowTotal],
'Focus Products'[Item Family],
[IsDM1Total],
'Focus Products'[Product Code-Desc],
[IsDM2Total]
),
[IsGrandTotalRowTotal],
0,
'Focus Products'[Item Category],
1,
[IsDM1Total],
1,
'Focus Products'[Item Family],
1,
[IsDM2Total],
1,
'Focus Products'[Product Code-Desc],
1
)

VAR __DS0SecondaryBase =
SUMMARIZE(__DS0Core, 'Date'[Year])

VAR __DS0Secondary =
TOPN(102, __DS0SecondaryBase, 'Date'[Year], 1)

VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'Date'[Year],
ASC
)
)

EVALUATE
__DS0Secondary

ORDER BY
'Date'[Year]

EVALUATE
__DS0BodyLimited

ORDER BY
[IsGrandTotalRowTotal] DESC,
'Focus Products'[Item Category],
[IsDM1Total],
'Focus Products'[Item Family],
[IsDM2Total],
'Focus Products'[Product Code-Desc],
[ColumnIndex]

[Syndicate_Admin]

Another option would be to connect to your dataset from Report Builder and then use the Query Designer there to build the dataset you need (instead of copy/paste of the DAX from Power BI).

Pat

[v-yingjl]

Hi @Unglaublichusa ,

After removing TOPN function, the DAX query would be like:

DEFINE
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'Date'[Month] ) ),
            NOT ( 'Date'[Month] IN { "October", "November", "December" } )
        )
    VAR __DS0FilterTable2 =
        TREATAS ( { "Domestic" }, 'Customers'[Sales Division] )
    VAR __DS0FilterTable3 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'SSM'[SalesPerson Territory] ) ),
            NOT ( 'SSM'[SalesPerson Territory] IN { "" } )
        )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL (
                'Focus Products'[Item Category],
                "IsGrandTotalRowTotal",
                'Focus Products'[Item Family],
                "IsDM1Total",
                'Focus Products'[Product Code-Desc],
                "IsDM2Total"
            ),
            'Date'[Year],
            __DS0FilterTable,
            __DS0FilterTable2,
            __DS0FilterTable3,
            "SumQuantity", CALCULATE ( SUM ( 'Invoice Details'[Quantity] ) )
        )
    VAR __DS0PrimaryWindowed =
        SUMMARIZE (
            __DS0Core,
            'Focus Products'[Item Category],
            [IsGrandTotalRowTotal],
            'Focus Products'[Item Family],
            [IsDM1Total],
            'Focus Products'[Product Code-Desc],
            [IsDM2Total]
        )
    VAR __DS0SecondaryBase =
        SUMMARIZE ( __DS0Core, 'Date'[Year] )
    VAR __DS0BodyLimited =
        NATURALLEFTOUTERJOIN (
            __DS0PrimaryWindowed,
            SUBSTITUTEWITHINDEX (
                __DS0Core,
                "ColumnIndex", __DS0SecondaryBase,
                'Date'[Year], ASC
            )
        )
EVALUATE
__DS0SecondaryBase
ORDER BY 'Date'[Year]
EVALUATE
__DS0BodyLimited
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'Focus Products'[Item Category],
    [IsDM1Total],
    'Focus Products'[Item Family],
    [IsDM2Total],
    'Focus Products'[Product Code-Desc],
    [ColumnIndex]

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Watsky]

Hey @Unglaublichusa ,

I don't think you really need to remove all of the TOPN variables listed in the query you can just change your first evaluate 

EVALUATE
__DS0Secondary

to 

EVALUATE
__DS0SecondaryBase

With that said you can also try this...

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Date'[Month])),
NOT('Date'[Month] IN {"October",
"November",
"December"})
)

VAR __DS0FilterTable2 =
TREATAS({"Domestic"}, 'Customers'[Sales Division])

VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('SSM'[SalesPerson Territory])),
NOT('SSM'[SalesPerson Territory] IN {""})
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Focus Products'[Item Category], "IsGrandTotalRowTotal",
'Focus Products'[Item Family], "IsDM1Total",
'Focus Products'[Product Code-Desc], "IsDM2Total"
),
'Date'[Year],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"SumQuantity", CALCULATE(SUM('Invoice Details'[Quantity]))
)

VAR __DS0PrimaryWindowed =
SUMMARIZE(
__DS0Core,
'Focus Products'[Item Category],
[IsGrandTotalRowTotal],
'Focus Products'[Item Family],
[IsDM1Total],
'Focus Products'[Product Code-Desc],
[IsDM2Total]
)

VAR __DS0SecondaryBase =
SUMMARIZE(__DS0Core, 'Date'[Year])

VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'Date'[Year],
ASC
)
)

EVALUATE
__DS0SecondaryBase

ORDER BY
'Date'[Year]

EVALUATE
__DS0BodyLimited

ORDER BY
[IsGrandTotalRowTotal] DESC,
'Focus Products'[Item Category],
[IsDM1Total],
'Focus Products'[Item Family],
[IsDM2Total],
'Focus Products'[Product Code-Desc],
[ColumnIndex]

[Unglaublichusa]

I am only getting one of the Table Fields with this change.

[Unglaublichusa]

Error Message on Validate; "Query (54, 1) Failed to resolve name '<pii>__DS0Secondary</pii>'. It is not a valid table, variable, or function name."

[Watsky]

Hey @Unglaublichusa ,  change __DS0Secondary to

__DS0SecondaryBase

[bushanbo]

Hi Watsky,

This helped in solving my problem of TOPN issue. Thank you.