Forum Discussion

arobinson's avatar
arobinson
Frequent Visitor
5 years ago
Solved

DAX Performance Slowness

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's avatar
    AlB
    5 years ago

    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's avatar
    AlB
    5 years ago

    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 

     

     

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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