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 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]

Need Help

7 Replies

  • Watsky's avatar
    Watsky
    Solution Sage
    4 years ago

    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's avatar
      Unglaublichusa
      New Member
      4 years ago

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

  • 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.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    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