Forum Discussion

BillyT_350's avatar
BillyT_350
Helper V
8 years ago
Solved

Slow Measures

I have report that analyzes data on customers. Data is filtered by three slicers, one for customer, another for data date, and the third for internal business unit (responsible for the customer).   ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    With this measure, you are calling, and calculating the [Scr%_ApptDesir] measure 7 times...it only needs to be calculated once.

     

    try this:

     

    Grd_ApptDesir =
VAR Score = [Scr%_ApptDesir]
RETURN
    SWITCH ( TRUE (),
        Score >= .9, "A",
        Score >= .8, "B",
        Score >= .7, "C",
        Score >= .6, "D",
        "F"
    )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

BillyT_350, this will optimize your measure a little bit:

 

Scr%_ApptDesir =
VAR FilteredTable =
    FILTER (
        'CUS_RANGE',
        'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE ( 'MTHLY'[IBU], "" )
            && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
            && 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR"
    )
RETURN
    IF (
        CALCULATE ( [m_ApptTimes] < AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), FilteredTable ),
        1,
        IF (
            CALCULATE ( [m_ApptTimes] > AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), FilteredTable ),
            0,
            CALCULATE (
                (
                    1
                        - DIVIDE (
                            ( [m_ApptTimes] - AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) ),
                            ( AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ) - AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) )
                        )
                ),
                FilteredTable
            )
        )
    )

You're using the same filter arugment in 3 different instances, so there's no need to scan the table 3 times to make the same table.  Just assign it to a variable and off you go.

 

I'm also a little curious about this expression.  is it a calculated column, or a measure?  If it's a measure, I would do this to further optimize:

 

Scr%_ApptDesir =
VAR FilteredTable =
    FILTER (
        'CUS_RANGE',
        'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE ( 'MTHLY'[IBU], "" )
            && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
            && 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR"
    )
VAR AverageMin =
    AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) 
/*Or this
	CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), 
		FilteredTable
	)
*/
VAR AverageMax =
    AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ) 
/*Or this
	CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), 
		FilteredTable
	)
*/
RETURN
    IF (
        CALCULATE ( [m_ApptTimes] < AverageMin, FilteredTable ),
        1,
        IF (
            CALCULATE ( [m_ApptTimes] > AverageMax, FilteredTable ),
            0,
            CALCULATE (
                (
                    1
                        - DIVIDE ( ( [m_ApptTimes] - AverageMin ), ( AverageMax - AverageMin ) )
                ),
                FilteredTable
            )
        )
    )

I can't tell if you want the TRUE/FALSE statement to be in the calculate expression, or if you want to compare the [m_ApptTimes] measure to the Average of the Min/Max column, and have both of them respect the filtered table variable.

 

Even still, the first expression will be at least a little faster.

BillyT_350's avatar
BillyT_350
Helper V
8 years ago

Anonymous the Scr%s are measures. A few tweaks, and I think this will work well.

 

To answer your question, we're merely checking to see if the calculated appointment times [m_ApptTimes] are greater than the min or the max allotted (contained with many others in table 'CUS_RANGE'). So, the two ways you've coded it would need to be altered a little to be like this:

RETURN
    IF (
        [m_ApptTimes] < CALCULATE (  AVERAGE('CUS_RANGE'[MIN_VALUE]), FilteredTable ),
        1, ...

or, 

VAR AverageMin =
    CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), 
		FilteredTable
	)

VAR AverageMax =
    CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), 
		FilteredTable
	)

RETURN
    IF (
        [m_ApptTimes] < AverageMin,
        1,
        IF (
            [m_ApptTimes] > AverageMax,
            0, ...

So, it would depend on which of the two methods used. Would it be faster to forgoe creating those variables, as each is only called twice anyways?


Thanks again! You've really advanced my knowledge of DAX. I didn't know that DAX could do half of the things that you've done in this code. I will go back and review more of my code, and see where I can use your suggestions.

I think the the VARs will be particularly useful.