Forum Discussion

nok's avatar
nok
Icon for Advocate II rankAdvocate II
2 years ago
Solved

Transform Performance Analyzer query into a new table

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]

 

 

  • 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

     

1 Reply

  • elitesmitpatel's avatar
    elitesmitpatel
    Icon for Solution Supplier rankSolution Supplier

    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