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 exact...
  • elitesmitpatel's avatar
    2 years ago

    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