Forum Discussion

Maltino's avatar
Maltino
Regular Visitor
3 years ago

Addcolumns + summarize + SUMX performance issue in matrix

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 2...
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
Maltino
Regular Visitor
3 years ago

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.