Forum Discussion

Unglaublichusa's avatar
Unglaublichusa
New Member
4 years ago

How to remove TOPN function for Paginated Report?

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 fr...
Need Help
v-yingjl's avatar
v-yingjl
Community Support
4 years ago

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.