Transform Performance Analyzer query into a new table

[nok]

Hello,

I have a visual table that contains 15 columns coming from 4 different related tables and with different filters applied in my dashboard page. I want to replicate this visual table with exactly the same data shown, but in a PBI table.

To do this, I went to the Performance Analyzer tab and copied the query from my visual table. However, when I go to "Create new table" and paste this query, PBI shows me errors in the code. How can I use this query to create a new table in my PBI with the same data as the visual table?

Below is the query for my visual table. When I try to use it to create a new table, the error is appearing in the "DEFINE" part and the "EVALUATE" part downwards. The error message is: the syntax of 'DEFINE' is incorrect.

// DAX Query
DEFINE
    VAR __DS0FilterTable =
        FILTER(
            KEEPFILTERS(VALUES('BL'[Source])),
            NOT(
                'BL'[Source] IN {"aaa",
                    "bbb",
                    "ccc"}
            )
        )

    VAR __DS0FilterTable2 =
        TREATAS({"BotL"}, 'BL'[Type])

    VAR __DS0FilterTable3 =
        TREATAS({"TAS",
            "MyProc"}, 'BL'[Local])

    VAR __DS0FilterTable4 =
        TREATAS({2024}, 'Calendar'[Year])

    VAR __DS0Core =
        SUMMARIZECOLUMNS(
            ROLLUPADDISSUBTOTAL(
                ROLLUPGROUP(
                    'Calendar'[MonRef],
                    'BL'[Evo],
                    'BL'[Local],
                    'BL'[Source],
                    'BL'[PO],
                    'BL'[Id],
                    'BL'[X-ID],
                    'BL'[Name],
                    'BL'[Status],
                    'BL'[O Status],
                    'ACR'[UB]
                ), "RowTotal"
            ),
            __DS0FilterTable,
            __DS0FilterTable2,
            __DS0FilterTable3,
            __DS0FilterTable4,
            "YTD", 'A_Measures'[YTD],
            "LD", 'A_Measures'[L D],
            "YTD_Dynamic_Forecast", 'A_Measures'[YTD_Dynamic_Forecast],
            "SUMUB", IGNORE(CALCULATE(SUM('ACR'[%_UB])))
        )

    VAR __DS0PrimaryWindowed =
        TOPN(
            502,
            __DS0Core,
            [RowTotal],
            0,
            'BL'[Name],
            1,
            'Calendar'[MonRef],
            1,
            'BL'[Evo],
            1,
            'BL'[Local],
            1,
            'BL'[Source],
            1,
            'BL'[PO],
            1,
            'BL'[Id],
            1,
            'BL'[X-ID],
            1,
            'BL'[O Status],
            1,
            'BL'[Status],
            1,
            'ACR'[UB],
            1
        )

EVALUATE
    __DS0PrimaryWindowed

ORDER BY
    [RowTotal] DESC,
    'BL'[Name],
    'Calendar'[MonRef],
    'BL'[Evo],
    'BL'[Local],
    'BL'[Source],
    'BL'[PO],
    'BL'[Id],
    'BL'[X-ID],
    'BL'[O Status],
    'BL'[Status],
    'ACR'[UB]

[elitesmitpatel · Accepted solution]

Try this 

NewTable =
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('BL'[Source])),
NOT('BL'[Source] IN {"aaa", "bbb", "ccc"})
)

VAR __DS0FilterTable2 =
TREATAS({"BotL"}, 'BL'[Type])

VAR __DS0FilterTable3 =
TREATAS({"TAS", "MyProc"}, 'BL'[Local])

VAR __DS0FilterTable4 =
TREATAS({2024}, 'Calendar'[Year])

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Calendar'[MonRef],
'BL'[Evo],
'BL'[Local],
'BL'[Source],
'BL'[PO],
'BL'[Id],
'BL'[X-ID],
'BL'[Name],
'BL'[Status],
'BL'[O Status],
'ACR'[UB]
), "RowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"YTD", 'A_Measures'[YTD],
"LD", 'A_Measures'[L D],
"YTD_Dynamic_Forecast", 'A_Measures'[YTD_Dynamic_Forecast],
"SUMUB", IGNORE(CALCULATE(SUM('ACR'[%_UB])))
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[RowTotal], DESC,
'BL'[Name], ASC,
'Calendar'[MonRef], ASC,
'BL'[Evo], ASC,
'BL'[Local], ASC,
'BL'[Source], ASC,
'BL'[PO], ASC,
'BL'[Id], ASC,
'BL'[X-ID], ASC,
'BL'[O Status], ASC,
'BL'[Status], ASC,
'ACR'[UB], ASC
)

RETURN
__DS0PrimaryWindowed