SWITCH performance

[f1254lipm]

Hi,

I have created this switch measure that computes values based on custom column name. The performance bottleneck is in the three variables Actuals, Budget and Forecast. Without the use of calculate all the visual loads in about 5s but with the calculate all the visual takes about 18s. I read many articles regarding the optimization but I am really stuck at this one. I would really appreciate any advice.

Thank you for your time and effort,
Filip

The measure:
Matrix Values =
VAR detail = SELECTEDVALUE('Column Names Flat'[Detail])
VAR lvl = SELECTEDVALUE('Column Names Flat'[Level])
VAR Actuals = CALCULATE(SUM('General Summary'[Actuals]),ALL(Scenario[Scenario]),Scenario[Scenario Type]="Actuals")
VAR Budget = CALCULATE(SUM('General Summary'[Budget]),ALL(Scenario[Scenario]),Scenario[Scenario Type]="Budget")
VAR Forecast = CALCULATE(SUM('General Summary'[Forecast]),ALL(Scenario[Scenario]),Scenario[Scenario Type]="Forecast")
VAR Komentar = [Comment Icon]
VAR M01 = SUM('General Summary'[budget_Y01M01])
VAR M02 = SUM('General Summary'[budget_Y01M02])
VAR M03 = SUM('General Summary'[budget_Y01M03])
VAR M04 = SUM('General Summary'[budget_Y01M04])
VAR M05 = SUM('General Summary'[budget_Y01M05])
VAR M06 = SUM('General Summary'[budget_Y01M06])
VAR M07 = SUM('General Summary'[budget_Y01M07])
VAR M08 = SUM('General Summary'[budget_Y01M08])
VAR M09 = SUM('General Summary'[budget_Y01M09])
VAR M10 = SUM('General Summary'[budget_Y01M10])
VAR M11 = SUM('General Summary'[budget_Y01M11])
VAR M12 = SUM('General Summary'[budget_Y01M12])
VAR Y01 = SUM('General Summary'[Total 2022])
VAR Y02 = SUM('General Summary'[Total 2023])
VAR Y03 = SUM('General Summary'[Total 2024])
RETURN
SWITCH(TRUE(),
detail ="Actuals",Actuals,
detail ="Budget",Budget,
detail ="Forecast",Forecast,
detail ="Komentář",Komentar,
detail ="01",M01,
detail ="02",M02,
detail ="03",M03,
detail ="04",M04,
detail ="05",M05,
detail ="06",M06,
detail ="07",M07,
detail ="08",M08,
detail ="09",M09,
detail ="10",M10,
detail ="11",M11,
detail ="12",M12,
detail = "Y1",Y01,
detail = "Year 2",Y02,
detail = "Year 3",Y03,
lvl = "Actuals",Actuals,
lvl = "Budget",Budget,
lvl = "Forecast",Forecast,
lvl ="Komentář",Komentar,
lvl = "Year 1",Y01,
lvl = "Year 2",Y02,
lvl = "Year 3",Y03
)

[v-cgao-msft · Accepted solution]

Hi @f1254lipm ,

Not sure, please try:

Matrix Values =
VAR detail =
    SELECTEDVALUE ( 'Column Names Flat'[Detail] )
VAR lvl =
    SELECTEDVALUE ( 'Column Names Flat'[Level] )
VAR ActualsBudgetForecast =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'General Summary'[Actuals],
            'General Summary'[Budget],
            'General Summary'[Forecast],
            'Scenario'[Scenario Type]
        ),
        ALL ( Scenario[Scenario] )
    )
VAR Actuals =
    SUMX (
        FILTER ( ActualsBudgetForecast, 'Scenario'[Scenario Type] = "Actuals" ),
        'General Summary'[Actuals]
    )
VAR Budget =
    SUMX (
        FILTER ( ActualsBudgetForecast, 'Scenario'[Scenario Type] = "Budget" ),
        'General Summary'[Budget]
    )
VAR Forecast =
    SUMX (
        FILTER ( ActualsBudgetForecast, 'Scenario'[Scenario Type] = "Forecast" ),
        'General Summary'[Forecast]
    )
VAR Komentar = [Comment Icon]
VAR M01 =
    SUM ( 'General Summary'[budget_Y01M01] )
VAR M02 =
    SUM ( 'General Summary'[budget_Y01M02] )
VAR M03 =
    SUM ( 'General Summary'[budget_Y01M03] )
VAR M04 =
    SUM ( 'General Summary'[budget_Y01M04] )
VAR M05 =
    SUM ( 'General Summary'[budget_Y01M05] )
VAR M06 =
    SUM ( 'General Summary'[budget_Y01M06] )
VAR M07 =
    SUM ( 'General Summary'[budget_Y01M07] )
VAR M08 =
    SUM ( 'General Summary'[budget_Y01M08] )
VAR M09 =
    SUM ( 'General Summary'[budget_Y01M09] )
VAR M10 =
    SUM ( 'General Summary'[budget_Y01M10] )
VAR M11 =
    SUM ( 'General Summary'[budget_Y01M11] )
VAR M12 =
    SUM ( 'General Summary'[budget_Y01M12] )
VAR Y01 =
    SUM ( 'General Summary'[Total 2022] )
VAR Y02 =
    SUM ( 'General Summary'[Total 2023] )
VAR Y03 =
    SUM ( 'General Summary'[Total 2024] )
RETURN
    SWITCH (
        TRUE (),
        detail = "Actuals", Actuals,
        detail = "Budget", Budget,
        detail = "Forecast", Forecast,
        detail = "Komentář", Komentar,
        detail = "01", M01,
        detail = "02", M02,
        detail = "03", M03,
        detail = "04", M04,
        detail = "05", M05,
        detail = "06", M06,
        detail = "07", M07,
        detail = "08", M08,
        detail = "09", M09,
        detail = "10", M10,
        detail = "11", M11,
        detail = "12", M12,
        detail = "Y1", Y01,
        detail = "Year 2", Y02,
        detail = "Year 3", Y03,
        lvl = "Actuals", Actuals,
        lvl = "Budget", Budget,
        lvl = "Forecast", Forecast,
        lvl = "Komentář", Komentar,
        lvl = "Year 1", Y01,
        lvl = "Year 2", Y02,
        lvl = "Year 3", Y03
    )

In addition, use the Performance Analyzer in Power BI Desktop to identify the most resource-intensive visualizations and DAX formulas in your reports. This tool can help you identify the exact cause of performance problems and guide you in optimizing your DAX expressions.

Remember that optimizing DAX expressions can be an iterative process. Keep monitoring the performance of your report and make adjustments as needed. Good luck!

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[f1254lipm]

Hi @v-cgao-msft Thank you for you answer! I implemented your proposed change and the report was slightly faster. Then I tried to dig more deeply into the performance analysis part and found out that the structure of the main view is not the best so I created separate views for just the three columns with calculate and removed calculate from the measure completely. The whole report now takes under 5s to load.

Thanks, Filip