How can i optimize the following switch statement and the underlying measure inside the statement

[Anonymous]

the following statement is taking 6000 ms to complete ,how can i optimise it 

the code for measure inside the statement is also taking 1800 ms , so i know that i need to optimize it as well the code being:
Agg_AttorneyCalc 10th Percentile FullTime:=SWITCH (
TRUE (),
[AttorneyCalc Selected] = "Total Compensation",
FORMAT (
CALCULATE (
[Total Compensation 10th Percentile 1],
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Full-Time / Part-Time] ),
All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
)
),
"$#,##0"
),
[AttorneyCalc Selected] = "Billing Rates", FORMAT ( CALCULATE ( [Standard Billing Rate 10th Percentile 1] ), "$#,##0" ),
[AttorneyCalc Selected] = "Billable Hours",
FORMAT (
CALCULATE (
[Actual Billable Hours 10th Percentile 1],
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Full-Time / Part-Time] ),
All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
),
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Actual Billable Hours] ),
All_FactEmployeeAndBenefits[Actual Billable Hours] > 1000
)
),
"#,##0"
),
[AttorneyCalc Selected] = "Billable Hour Goals",
FORMAT (
CALCULATE (
[Billable Hour Goal 10th Percentile 1],
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Full-Time / Part-Time] ),
All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
),
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Billable Hour Goal] ),
All_FactEmployeeAndBenefits[Billable Hour Goal] > 1000
)
),
"#,##0"
),
[AttorneyCalc Selected] = "Annual Base Salary",
FORMAT (
CALCULATE (
[Base Compensation 10th Percentile 1],
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Full-Time / Part-Time] ),
All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
)
),
"$#,##0"
),
[AttorneyCalc Selected] = "Annual Bonus",
FORMAT (
CALCULATE (
[Annual Bonus 10th Percentile 1],
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Full-Time / Part-Time] ),
All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
)
),
"$#,##0"
),
[AttorneyCalc Selected] = "Annual Bonus Percentage",
FORMAT (
CALCULATE (
[Annual Bonus Percentage 10th Percentile 1],
FILTER (
VALUES ( All_FactEmployeeAndBenefits[Full-Time / Part-Time] ),
All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
)
),
"0.0%"
),
BLANK ()
)

[SpartaBI]

@Anonymous did some optimizations and also a lot of code cleaning to make it more readable.
Could be that the measure itself is the issue but anyway I've put that in a VAR so in case the engine couldn't do that optimization on its own than this alone could have a big impact. Please check and update how much time it took now.

Agg_AttorneyCalc 10th Percentile FullTime :=
VAR _AttorneyCalc_Selected = [AttorneyCalc Selected]
VAR _filter_1386 =
    FILTER (
        ALL ( All_FactEmployeeAndBenefits[Full-Time / Part-Time] ),
        All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
    )
RETURN
    SWITCH (
        TRUE (),
        _AttorneyCalc_Selected = "Total Compensation",
            FORMAT (
                CALCULATE (
                    [Total Compensation 10th Percentile 1],
                    KEEPFILTERS ( _filter_1386 )
                ),
                "$#,##0"
            ),
        _AttorneyCalc_Selected = "Billing Rates",
            FORMAT (
                [Standard Billing Rate 10th Percentile 1],
                "$#,##0"
            ),
        _AttorneyCalc_Selected = "Billable Hours",
            FORMAT (
                CALCULATE (
                    [Actual Billable Hours 10th Percentile 1],
                    FILTER (
                        SUMMARIZE (
                            All_FactEmployeeAndBenefits,
                            All_FactEmployeeAndBenefits[Full-Time / Part-Time],
                            All_FactEmployeeAndBenefits[Actual Billable Hours]
                        ),
                        All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
                            && All_FactEmployeeAndBenefits[Actual Billable Hours] > 1000
                    )
                ),
                "#,##0"
            ),
        _AttorneyCalc_Selected = "Billable Hour Goals",
            FORMAT (
                CALCULATE (
                    [Billable Hour Goal 10th Percentile 1],
                    FILTER (
                        SUMMARIZE (
                            All_FactEmployeeAndBenefits,
                            All_FactEmployeeAndBenefits[Full-Time / Part-Time],
                            All_FactEmployeeAndBenefits[Billable Hour Goal]
                        ),
                        All_FactEmployeeAndBenefits[Full-Time / Part-Time] = 1386
                            && All_FactEmployeeAndBenefits[Billable Hour Goal] > 1000
                    )
                ),
                "#,##0"
            ),
        _AttorneyCalc_Selected = "Annual Base Salary",
            FORMAT (
                CALCULATE (
                    [Base Compensation 10th Percentile 1],
                    KEEPFILTERS ( _filter_1386 )
                ),
                "$#,##0"
            ),
        _AttorneyCalc_Selected = "Annual Bonus",
            FORMAT (
                CALCULATE (
                    [Annual Bonus 10th Percentile 1],
                    KEEPFILTERS ( _filter_1386 )
                ),
                "$#,##0"
            ),
        _AttorneyCalc_Selected = "Annual Bonus Percentage",
            FORMAT (
                CALCULATE (
                    [Annual Bonus Percentage 10th Percentile 1],
                    KEEPFILTERS ( _filter_1386 )
                ),
                "0.0%"
            ),
        BLANK ()
    )

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

unfortunately the var filter is not working and without using that var filter its taking more or less the same time. I am attaching the error as shown. 

I am also attaching the code for the one i have put in SS so that you can refer that as well for optimization

10th percentile_test = var a=SWITCH (FIRSTNONBLANK(values(DimToggleValues[Dimensions]),1),

"# of Attorneys",CALCULATE([Agg_AttorneyCalc 10th Percentile FullTime],FILTER(All_DimAttorneyBuckets,All_DimAttorneyBuckets[Attorney Bucket]=SELECTEDVALUE(DimToggleValues[Values]))),

"Practice Area",CALCULATE([Agg_AttorneyCalc 10th Percentile FullTime],FILTER(DimParalegalPractice,DimParalegalPractice[ParalegalPracticeArea]=SELECTEDVALUE(DimToggleValues[Values]))),

"Location",CALCULATE([Agg_AttorneyCalc 10th Percentile FullTime],FILTER(All_DimFirmByLocationAndZipCode,All_DimFirmByLocationAndZipCode[Location]=SELECTEDVALUE(DimToggleValues[Values]))),

"Class Year",CALCULATE([Agg_AttorneyCalc 10th Percentile FullTime],FILTER(All_FactEmployeeAndBenefits,All_FactEmployeeAndBenefits[Class Year_Bucket]=SELECTEDVALUE(DimToggleValues[Values]))))

return format(a,"#,##0")

[SpartaBI]

@Anonymous right, my bad. Forgot you can't use the syntax sugaring in a VAR. I edited that VAR to be the explicit table filter in the original message. Please check now and update the impact.
I will also look now at the measures you sent.

[Anonymous]

Thanks for the fast response,
its still giving the same error mate
the same dailogue box appears

[SpartaBI]

@Anonymous strange. Anyway, this is your big issue. Change the code of the measure to this:

10th percentile_test =
VAR _check =
    FIRSTNONBLANK (
        VALUES ( DimToggleValues[Dimensions] ),
        1
    )
VAR _selected_togg =
    SELECTEDVALUE ( DimToggleValues[Values] )
VAR a =
    SWITCH (
        _check,
        "# of Attorneys",
            CALCULATE (
                [Agg_AttorneyCalc 10th Percentile FullTime],
                KEEPFILTERS ( All_DimAttorneyBuckets[Attorney Bucket] = _selected_togg )
            ),
        "Practice Area",
            CALCULATE (
                [Agg_AttorneyCalc 10th Percentile FullTime],
                KEEPFILTERS ( DimParalegalPractice[ParalegalPracticeArea] = _selected_togg )
            ),
        "Location",
            CALCULATE (
                [Agg_AttorneyCalc 10th Percentile FullTime],
                KEEPFILTERS ( All_DimFirmByLocationAndZipCode[Location] = _selected_togg )
            ),
        "Class Year",
            CALCULATE (
                [Agg_AttorneyCalc 10th Percentile FullTime],
                KEEPFILTERS ( All_FactEmployeeAndBenefits[Class Year_Bucket] = _selected_togg )
            )
    )
RETURN
    FORMAT (
        a,
        "#,##0"
    )

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

I also tried with eager evaluation , just like my code yours is reducing the duration by 200 ms but the time taken is anyways 6500 ms, we have to work on the underlying measure to decrease the time imo.
also can you can you try to represent the same code in form of nested if statement using variables

, i read somewhere that it also sometimes works faster that the switch statement

[SpartaBI]

@Anonymous 
You are saying that the new code for [ 10th percentile_test] didn't make a lot of difference? This is really unlikely becase previosly you did a filter on the entire table and I changed it to only 1 column. 
Also, I edited again the final code for the [ Agg_AttorneyCalc 10th Percentile FullTime]. I still did a VAR but used the KEEPFILTERS inside the calculate.

Please check now with all the changes and let me know.

Regarding replacing the switch with IF, they already did new optimziaions to the SWITCH function, so I don't think this is the big problem. 
Update me with the current status

[Anonymous]

the updated Agg_AttorneyCalc 10th Percentile FullTime code of yours is taking even more time than the original code strangely , the original one completes in around 1600 ms while yours is taking 2300 ms

[SpartaBI]

@Anonymous how are you checking this? With PBI performance analyzer or with DAX stuido? 

[Anonymous]

Performance analyzer 

[SpartaBI]

@Anonymous ok, this is not the way to do that.
Performance analyzer is a the place to start but you need to take the queries from it to DAX studio for testing and optimziaing. In PBIX there is always the cache on the 2nd run. In DAX studio you can clear the cache and also have much more information. You need to go do some reading and watching videos on how to test and optimize. Everything you need is at www.sqblbi.com and their youtube channel. 

[Anonymous]

I did uss DAX studio as well--> clear cache and then run , it's taking more time

[SpartaBI]

Ok, 🤷‍.

Not much more I can do in the scope of forum messages. This requires looking deep in the model itself, the visual query you are creating etc, looking at the query plan and server timing and analyzing it.

[SpartaBI]

@Anonymous ok, you have a very big suspect in the measure 🙂 Will optimize it and send

[Anonymous]

Thanks for the help, 
also if i am taking Agg_attorney calc 10th percentile fulltime

 as a variable it is just taking 1500 ms but then the output is coming out to be wrong ,why is that as i can see you have also not taken that measure as the variable 

[SpartaBI]

@Anonymous you can't use a VAR that is after used in the calculate 1st argument. You are losing the impact of the CALCULATE.