DAX Performance Slowness

[arobinson]

Hey all, 

Working through a few different querries on a single table visual. This is preventing slowness in the table. Please provide suggestions on how to make the following a little faster. 

Total % =

VAR _selvalue =

SELECTEDVALUE ( 'Credentialing Name Sheet'[Territory Type])

RETURN

SWITCH (

TRUE (),

_selvalue = "D1"

|| _selvalue = "D2"

|| _selvalue = "D3", ( [Communication Total] * .4 ) + ( [Productivity Total] * .6 ),

_selvalue = "DG1", ( [Communication Total] * .4 ) + ( [Productivity Total] * .6 ),

_selvalue = "DG2", ( [Communication Total] * .7 ) + ( [Productivity Total] * .3 ),

_selvalue = "DG3", ( [Communication Total] * .95 ) + ( [Productivity Total] * .05 ),

_selvalue = "A1" ,[Productivity Total] ,

_selvalue = "A2" , [Productivity Total] ,

_selvalue = "A3", ( [Communication Total] * .05) + ( [Productivity Total] * .95 ),

_selvalue = "O1", [Communication Total],

_selvalue = "O2", ( [Communication Total] * .4 ) + ( [Productivity Total] * .6),

_selvalue = "O3", [Productivity Total] ,

_selvalue = "NR1", ( [Communication Total] * .4 ) + ( [Productivity Total] * .6))

AND 

Communication Total =

IFERROR(VAR _selvalue =

SELECTEDVALUE ( 'Credentialing Name Sheet'[Territory Type] )

RETURN

SWITCH (

TRUE (),

_selvalue = "D1"

|| _selvalue = "D2"

|| _selvalue = "D3", ( 'Range Goals'[Developed Call % to Goal] + 'Range Goals'[Call Quality % to Goal] ) / 2,

_selvalue = "DG1"

|| _selvalue = "DG2"

|| _selvalue = "DG3", ( 'Range Goals'[Developing Calls % to Goal] + 'Range Goals'[Call Quality % to Goal] ) / 2,

_selvalue = "A1", ( 'Range Goals'[Admin Call % to Goal] ),

_selvalue = "O1", ( 'Range Goals'[Other Call % to Goal] + 'Range Goals'[Call Quality % to Goal] ) / 2,

_selvalue = "O3", ( 'Range Goals'[Other Call % to Goal] )),Blank()

)

[AlB · Accepted solution]

Hi @arobinson 

Total % =
VAR _selvalue =
    SELECTEDVALUE ( 'Credentialing Name Sheet'[Territory Type] )
VAR productivityTotal_ = [Productivity Total]
VAR communicationTotal_ = [Communication Total]
RETURN
    SWITCH (
        TRUE (),
        _selvalue = "D1"
            || _selvalue = "D2"
            || _selvalue = "D3",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 ),
        _selvalue = "DG1",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 ),
        _selvalue = "DG2",
             ( communicationTotal_ * .7 ) + ( productivityTotal_ * .3 ),
        _selvalue = "DG3",
             ( communicationTotal_ * .95 ) + ( productivityTotal_ * .05 ),
        _selvalue = "A1", productivityTotal_,
        _selvalue = "A2", productivityTotal_,
        _selvalue = "A3",
             ( communicationTotal_ * .05 ) + ( productivityTotal_ * .95 ),
        _selvalue = "O1", communicationTotal_,
        _selvalue = "O2",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 ),
        _selvalue = "O3", productivityTotal_,
        _selvalue = "NR1",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@arobinson 

Note you are invoking the measures many times. By using variables you can have the measures executed just once. You haven't posted the code for [Productivity Total]. Perhaps that can be optimized as well. I don't see much room for performance improvement in [Communication Total], given its simplicity.  You can though use IN as a way to make the code more legible and more convenient to create. For instance:

_selvalue  IN {"D1", "D2", "D3"}

is equivalent to

_selvalue = "D1"
|| _selvalue = "D2"
|| _selvalue = "D3"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @arobinson 

Total % =
VAR _selvalue =
    SELECTEDVALUE ( 'Credentialing Name Sheet'[Territory Type] )
VAR productivityTotal_ = [Productivity Total]
VAR communicationTotal_ = [Communication Total]
RETURN
    SWITCH (
        TRUE (),
        _selvalue = "D1"
            || _selvalue = "D2"
            || _selvalue = "D3",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 ),
        _selvalue = "DG1",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 ),
        _selvalue = "DG2",
             ( communicationTotal_ * .7 ) + ( productivityTotal_ * .3 ),
        _selvalue = "DG3",
             ( communicationTotal_ * .95 ) + ( productivityTotal_ * .05 ),
        _selvalue = "A1", productivityTotal_,
        _selvalue = "A2", productivityTotal_,
        _selvalue = "A3",
             ( communicationTotal_ * .05 ) + ( productivityTotal_ * .95 ),
        _selvalue = "O1", communicationTotal_,
        _selvalue = "O2",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 ),
        _selvalue = "O3", productivityTotal_,
        _selvalue = "NR1",
             ( communicationTotal_ * .4 ) + ( productivityTotal_ * .6 )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers