Low performance issue with calculcation group

[Tracy000]

Hi team,

I got performance issue after using calcuation group in one of my PBI report.

This PBI report get data directly from a semantic model, it shows some measures by quarterly, yearly, YoY, like following screenshot.
Totally have 37 measures.

If I just show the data by quarterly, there is no any issue, because I don't need to use calculation group, the performance is good, take around 3-4 secs to show the result.(if only with green part header)

But if I add the FY 2022 - FY 2025 or FY22-23 - FY24-25, I have to add the calculation group I created, it will be slow, take more than 20 secs. (green + yellow color header together)

I have a separate table to save the header like Q1'FY22 Actual, Q1'FY25 Current Flash...etc, this table join with fact table, but for FY related value, it can't directly sum up, I have to calculate in calculation group.

Besides this dashboard, my other dashboards performance also affected by calculation group, without calculation group, I need to create so many measures, with calculation group, the performance is not good.

Do you have any suggestion about the performance issue with calculation group? thank you.

This is the logic of this calculaction group item:

CalGroup_measure =

VAR fy_previous_actual = //use for FY xxxx Actual calculation, like 'FY 2024 Actual'

    CALCULATE (

        SELECTEDMEASURE(),

        FILTER ( ALLSELECTED ( 'fact'[scenario] ), 'fact'[scenario] = "Actual" ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[fiscal year] ) = IFERROR(VALUE(RIGHT(LEFT(SELECTEDVALUE ( BS_header[header order] ) ,7), 4)),BLANK())),

        FILTER ( ALL ( 'date' ), 'date'[relative year] < 0 )

    )

VAR fy_pre_actual =   //use for FYxx-yy calculcation, the yy part

    CALCULATE (

        SELECTEDMEASURE(),

        FILTER ( ALLSELECTED ( 'fact'[scenario] ), 'fact'[scenario] = "Actual" ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[fiscal year] ) = IFERROR(VALUE(CONCATENATE("20", LEFT(RIGHT(SELECTEDVALUE ( BS_header[header order] ) ,3), 2))),BLANK())),

        FILTER ( ALL ( 'date' ), 'date'[relative year] < 0 )

    )

VAR fy_pre_pre_actual =   //use for FYxx-yy calculcation, the xx part

    CALCULATE (

        SELECTEDMEASURE(),

        FILTER ( ALLSELECTED ( 'fact'[scenario] ), 'fact'[scenario] = "Actual" ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[fiscal year] ) = IFERROR(VALUE(CONCATENATE("20", LEFT(RIGHT(SELECTEDVALUE ( BS_header[header order] ) ,3), 2))-1),BLANK())),

        FILTER ( ALL ( 'date' ), 'date'[relative year] < 0 )

    )

VAR fy_pre_actual_to_cur =    //use for FYxx-yy calculation, the xx part (yy is current year)

    CALCULATE (

        SELECTEDMEASURE(),

        FILTER ( ALLSELECTED ( 'fact'[scenario] ), 'fact'[scenario] = "Actual" ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[fiscal year] ) = IFERROR(VALUE(CONCATENATE("20", LEFT(RIGHT(SELECTEDVALUE ( BS_header[header order] ) ,6), 2))),BLANK())),

        FILTER ( ALL ( 'date' ), 'date'[relative year] = -1 )

    )

VAR current_year =

    CALCULATE (

        DISTINCT ( 'date'[fiscal year] ),

        FILTER ( ALL ( 'date' ), 'date'[relative year] = 0 )

    )

VAR actual_flash_current_year =

    CALCULATE (

        SELECTEDMEASURE(),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( scenario[scenario relative] ) = "Actual" ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[fiscal year] ) = current_year ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[relative quarter] ) < 0)

    )

    +

    CALCULATE (

        SELECTEDMEASURE(),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( scenario[scenario relative] ) = "Current Flash" ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[fiscal year] ) = current_year ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[relative quarter]) >= 0)

    )

VAR budget_current_year =

    CALCULATE (

        SELECTEDMEASURE(),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( scenario[scenario relative] ) = "Budget" ),

        FILTER ( ALLSELECTED ( 'fact' ), RELATED ( 'date'[fiscal year] ) = current_year )

    )  

VAR fin_value =

SWITCH (

    SELECTEDVALUE ( BS_header[header order] ),

    "FY" & " " & RIGHT(LEFT(SELECTEDVALUE ( BS_header[header order] ) ,7), 4) & " " & "Actual", fy_previous_actual,

    "FY" & " " & current_year & " " & "Current Flash", actual_flash_current_year,  

    "FY" & " " & current_year & " " & "Budget", budget_current_year,              

    "FY" & LEFT(RIGHT(SELECTEDVALUE ( BS_header[header order] ) ,6), 2) & "-" & RIGHT(current_year, 2) & " ",

        FORMAT (

            DIVIDE ( actual_flash_current_year, fy_pre_actual_to_cur) - 1,

            "0.0%"

        ),

    "FY" & LEFT(RIGHT(SELECTEDVALUE ( BS_header[header order] ) ,6), 2) & "-"                                      

         & LEFT(RIGHT(SELECTEDVALUE ( BS_header[header order] ) ,3),2) & " ",

        FORMAT (

            DIVIDE ( fy_pre_actual, fy_pre_pre_actual) - 1,

            "0.0%"

        ),

    SELECTEDMEASURE ()

)  

RETURN

fin_value

[v-menakakota]

Hi @Tracy000 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @Tracy000 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-menakakota]

Hi @Tracy000 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota · Accepted solution]

Hi @Tracy000 ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

As per my understanding you can follow these steps to improve performance:
Avoid applying ALLSELECTED on entire tables. Instead, apply filters only on necessary columns.
Use DAX Studio to analyze query execution time. Identify slow operations (especially FILTER, ALLSELECTED, and CALCULATE).
Create a static table that maps BS_header values to fiscal years. Use this table in relationships instead of extracting values dynamically with LEFT, RIGHT, or CONCATENATE.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.