0

-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

RADO
  • 7,733
  • 3
  • 19
  • 33
ema
  • 17
  • 3

1 Answers1

0

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!

StelioK
  • 1,771
  • 1
  • 11
  • 21
  • All right. But I use SSAS 2012 and I think it doesn't support VAR variables in DAX. It's only from SSAS 2016. – ema Sep 11 '18 at 21:15
  • OK let me make some adjustments – StelioK Sep 11 '18 at 21:19
  • @ema please see my edits, I misread the original measure, I apologize. – StelioK Sep 11 '18 at 21:28
  • thanks. It returns the same result than the initial query. But time duration is similar . Each time I select a new date in Excel, the refresh spend between 18s - 51s. Do you think an other rewrite is possible ? – ema Sep 12 '18 at 15:13
  • as the DIVIDE function is very fast, I don't know if it would be more efficient to apply directly the condition to the result of the DIVIDE ? Something like that : result =DIVIDE ( SUM ( 'Fact1'[ColumnA] ), SUM ( 'Fact2'[ColumnB] ) ). IF result =0 then BLANK, IF result ISBLANK then 99 else result – ema Sep 12 '18 at 15:23
  • That's definitely worth a shot! – StelioK Sep 12 '18 at 16:12
  • I added that logic into my answer. I think if you just create a calculated column using divide and measure against that it should be much more performant. – StelioK Sep 12 '18 at 16:18
  • I'm not sure if it's possible to create a calculate column as I have no relations defined between my 2 facts tables!We have only relations between facts tables and dimensions tables. Dim tables are common to both facts tables. The RELATED() function doesn't work. – ema Sep 13 '18 at 09:18
  • I've added caculate column "result" But when I setup the "measure1" which calls the calculate column the following error is displayed in VisualStudio: #ERROR : Semantic Error: "The value for column 'result' in table 'Fact2' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single.. – ema Sep 13 '18 at 09:19