DAX : optimize measure in SSAS 2012 tabular model

Question

-I use SSAS 2012 SP4 tabular version. The cube contains 2 facts tables and each one uses the same dimension tables (=7 dimension tables). There is no relation defined between 2 fact tables.

-The following measure is setup in Fact2 table:

Measure1 :=
IF (
    SUM ( Fact1[ColumnA] ) = 0,
    BLANK (),
    IF (
        SUM ( Fact2[ColumnB] ) > 0,
        SUM ( Fact1[ColumnA] ) / SUM ( Fact2[ColumnB] ),
        99
    )
)

My Excel report is very long to refresh when I display this measure and several attributes.

-When I active a profiler trace I can see that the time spend in the Formula Engine is 80%. I have tried to rewrite the query with using the function DIVIDE() like this :

Measure1 := DIVIDE(sum(Fact1[ColumnA])/sum(Fact2[ColumnB]),99)

In this case the report is fast and query duration is better. But as I have removed the IF function I don't check any more "if sum(Fact1[ColumnA])=0"

It is possible to refactor this DAX formula in order to improve performance. And keeping the check "IF sum(Fact1[ColumnA])=0 THEN BLANK()" ?

Thanks a lot for your help Em

StelioK StelioK · Accepted Answer · 2018-09-11T20:26:47

Sure, try this:

Measure 1 :=
VAR SUMA =
    SUM ( 'Fact1'[ColumnA] )
VAR SUMB =
    SUM ( 'Fact2'[ColumnB] )
VAR QUOT =
    DIVIDE ( SUM ( 'Fact1'[ColumnA] ), SUM ( 'Fact2'[ColumnB] ) )
RETURN
    SWITCH ( TRUE (), SUMA = 0, BLANK (), SUMB > 0, QUOT, 99 )

If you can't use Variables in your measures, try this:

Measure1 :=
SWITCH (
    TRUE (),
    SUM ( 'Fact1'[ColumnA] ) = 0, BLANK (),
    SUM ( 'Fact2'[ColumnB] ) > 0, DIVIDE ( SUM ( 'Fact1'[ColumnA] ), SUM ( 'Fact2'[ColumnB] ) ),
    99
)

Another method would be to create a calculated column like so:

result := DIVIDE ( SUM ( 'Fact1'[ColumnA] ), SUM ( 'Fact2'[ColumnB] )
)

Then, write a measure against it like this:

Measure1 :=
SWITCH ( TRUE (), [result] = 0, BLANK (), [result] > 0, [result], 99 )

I haven't tested either of these so I am not sure how their performance will be.

Hope it helps!

DAX : optimize measure in SSAS 2012 tabular model

1 Answers