Hi,

I have been trying to optimize a few DAX calculations that I have in my cube and ran into this problem

Basically my DAX Measure 

Counterparty Collateral Base DKK:=

[Counterparty Collateral Contracts DKK (filtered)] 
+ 
CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)

is pretty fast,

whereas as soon as I change the 2nd part to only add Account Balance if its>0, it takes ages i.e.

Counterparty Collateral Base DKK:=

[Counterparty Collateral Contracts DKK (filtered)] 
+ 
IF(CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)>0,

CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16),0)

Offcourse because I check every row I suppose with that "if" statement. Question is how do I improve it?

By the Way: [Account Balances DKK] is in the table - Account Balances

                    [Counterparty Collateral Contracts DKK (filtered)]  is in the table- Open Positions

This calculated measure [Counterparty Collateral Base DKK] is in Open Positions

And the query in MDX, that I run to pull this measure, looks like

 select {[Measures].[Counterparty Collateral Base DKK]
}on 0,
Non Empty {[Accounts].[Counterpart ID].children
  *[Base Currencies].[Currency Code].children
  *[Products].[Product Name].children
  } on 1
from [model]
where [Dates].[Date].&[2015-06-30T00:00:00]

The requirement is such that I have to add the measures in two separate tables.

How can I improve the performance when adding the filter?

Any help would be appreciated.

                         

Hi Saugat,

According to your description, you want to improve the performance of the MDX and DAX query. Right?

For your first DAX query, since you only want to return positive value, using IF() is the most efficient way. And there's no other function which can replace this function and achieve same logic. I don't think there's any way can improve the performance of that DAX query.

For your second MDX query, always filter a set before using it in a crossjoin to reduce the cube space before performing the crossjoin. Use EXISTS rather than filtering on member properties to avoid a slow execution path. Use the NonEmpty and Exists functions to enable the query execution engine to use bulk evaluation mode.

For more information, please see:

http://www.sqlpass.org/Portals/333/Alberto%20Ferrari_Optimizing%20DAX%20Queries.pdf

https://technet.microsoft.com/en-us/library/Cc966527.aspx?f=255&MSPPError=-2147217396

https://www.packtpub.com/books/content/query-performance-tuning-microsoft-analysis-services-part-2

Regards,

Hi Simon,

Thanks for your response. I was only looking for optimizing the DAX. The MDX is what it should be, since I am trying to dump all rows into a table (using openquery).

Got around somewhat by using a combination of SUMX, ADDCOLUMNS AND SUMMARIZE. something like this

Counterparty Collateral Base DKK:=

[Counterparty Collateral Contracts DKK (filtered)] 
+ 

SUMX(

          ADDCOLUMNS

          (

             SUMMARIZE('AccountBalances',

                                    CounterpartID

                                  )

               , "Val"

              , CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16),0)

        )

        ,IF(Val>0,Val,0)

  )

Though not super fast, but its reasonable.

This however seems to be a generic problem. Whenever I use an IF statement in my DAX, the measure becomes extremely slow. Any pointers?

E.g. [FX Risk Weighted DKK (BC + QC)]  is a DAX measure and when I retrieve it using MDX, it is extremely fast .

This is the MDX to retrieve the measure (simple & straightforward)

select {
[Measures].[FX Risk Weighted DKK (BC + QC, Long)]
}on 0,
Non Empty {[Accounts].[Counterpart ID].children
  *[Base Currencies].[Currency Code].children
  *[Products].[Product Name].children
  } on 1
from [model]
where [Dates].[Date].&[2015-06-30T00:00:00]

However, as soon as I change the DAX measure to: IF([FX Risk Weighted DKK (BC + QC)]>0,[FX Risk Weighted DKK (BC + QC)]), the same MDX to retrieve the measure is extremely slow.

I have  SP2 of SQL2012 Analysis Services (Since there was this article

https://support.microsoft.com/en-us/kb/2733091)

But it should have been fixed by the service pack.

Please help.

In your original slow example, can you try removing the ,0 (the last param of IF). Just a guess. What you don't want is to fill up your cube space with bunches of 0's for all possible dimension combinations. If(measure>0,measure) will return blank if measure is <=0 and it may possibly perform better.