Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
f1254lipm
Frequent Visitor

SWITCH performance

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
)

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.