Addcolumns + summarize + SUMX performance issue in matrix

[Maltino]

Hi,

Im trying to get better performance on a popular report in my portfolio, but I struggle to come up with any improvements.

The report page uses a matrix with 14 columns and between 5 and 20 rows depending on the users filters.

The rows represent our organisation-hierarchy, and this hierarchy of course needs specific aggregations. These aggregations and the DAX-code I made to handle this is according to my testing what causes slow performance.

Current performance is around 1500ms (DAX query) for each refresh or drill-action on the matrix.

During my testing I have found that my measures with ADDCOLUMNS(SUMMARIZE.. + SUMX are taking the most time to run.

Here is an example:

*Measure* Hierarki sum = 
// *Measure* is summarized at TC-level 

// IF-case needed to get correct aggregation for certain ID_PL:s
IF(
    HASONEVALUE('DIM-Organization'[id_tc]),
        var summarizedTable = ADDCOLUMNS (
            SUMMARIZE(
                'DIM-Organization',
                'DIM-Organization'[id_dep],
                'DIM-Organization'[id_pl],
                'DIM-Organization'[id_tc]),
                "MeasureArea", [Measure]
)


Return 
SUMX(summarizedTable,[MeasureArea])
,
var summarizedTable = ADDCOLUMNS (
SUMMARIZE(
            'DIM-Organization',
            'DIM-Organization'[id_dep],
            'DIM-Organization'[id_pl],
            'DIM-Organization'[id_tc]),
            "MeasureArea", CALCULATE([Measure],'DIM-Organization'[id_tc (groups)] ="Other")
)


Return 
SUMX(summarizedTable,[MeasureArea])

The measure that is input above should not be very heavy to run i belive:

Measure = DIVIDE(SUM(*this*),SUM(*that*),BLANK())

It is difficult to move all this to a calculated table/column since this measures are affected by a date-dimension. The date period can be anything from 1 day to two years.

The matrix 14 columns includes 5 or 6 measures like this so i can understand that it takes some time for it to load, but i would love some input on how i can make it a little better!

[johnt75]

In the code you've posted you're not using the MeasureArea calculated column at all, in either branch, so there is no point calculating it. You could get rid of the ADDCOLUMNS and the MeasureArea column and that should speed things up. Try

*Measure* Hierarki sum =
// *Measure* is summarized at TC-level 
// IF-case needed to get correct aggregation for certain ID_PL:s
VAR summarizedTable =
    SUMMARIZE (
        'DIM-Organization',
        'DIM-Organization'[id_dep],
        'DIM-Organization'[id_pl],
        'DIM-Organization'[id_tc]
    )
RETURN
    SUMX (
        summarizedTable,
        IF ( HASONEVALUE ( 'DIM-Organization'[id_tc] ), [JphArea], [Measure] )
    )

[Maltino]

Thanks for your reply John! Unfortunatly i made some errors in my first post while editing the code to make it more anonymous, I have corrected it now. But still, i tried your suggestion to remove ADDCOLUMNS:

Measure hiearachy sum = 
// Measure is summarized at TC-level

var summarizedTable = 
	SUMMARIZE(
		'DIM-Organization',
		'DIM-Organization' [id_dep],
		'DIM-Organization' [id_tc]
	)

Return
IF(
	HASONEVALUE('DIM-Organization' [id_tc]),
		SUMX(summarizedTable,[Measure]),
		CALCULATE(
			SUMX(summarizedTable,[Measure]),
			'DIM-Organization' [id_tc (groups)] = "Other"
	)
)  

This works fine, but no obvious gain in performance when comparing to the previous code.

I have noticed that the IF-case in measure tends to add 40-50% execution-time on the DAX-Query.

Yesterday i found one way to help ease the load from the matrix that i think is pretty cool, even if it is kind of a work-around.

1. Add a measure that evaluates on wich hierachy-level a row are:

Hierachy Level =
VAR IsLineInScope = ISINSCOPE ( 'DIM-Organization'[TactCircuitMediumName] )
VAR IsDepInScope = ISINSCOPE ( 'DIM-Organization'[DepartmentExtendedName] )
VAR IsDivInScope = ISINSCOPE ( 'DIM-Organization'[DivisionExtendedName] )

VAR Result = 
SWITCH ( TRUE (),
 IsLineInScope, "Line",
 IsDepInScope, "Department",
 IsDivInScope, "Division",
 BLANK() )

 RETURN Result 

2. Make a measure with SWITCH() that runs different measures depending on what level is selected. 

Measure that eases the load = 
SWITCH ([Hierachy Level],
 "Division", BLANK(),
 "Department", [Measure hierachy sum],
 "Line", [Measure])

So basicly, on the lowest level we run just the basic measure, without SUMMARIZE, SUMX or the IF-case. This change to my model dropped the DAX-query time with about 50% on refresh or drill-actions in the Matrix.