Optimize filtering | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Optimize filtering

Hi there, I’ve created a Calculated Measure that returns the number of buying customers from a cube with sales/customer data. I’m using the following code: Iif(
Count(
Filter(
Descendants( [Customers].CurrentMember, [Customers].[Customer] )
, ([Net Retail Price] <> null) Or ([Return Amount] > 0)
)
) = 0, null,
Count(
Filter(
Descendants( [Customers].CurrentMember, [Customers].[Customer] )
, ([Net Retail Price] <> null) Or ([Return Amount] > 0)
)
)
) This measure is performing very poorly. Queries take up to 5 minutes to complete. I tried playing with local variables to be able to only run the count(filter()) statement once, but that doesn’t seem to be possible. The Filter action is the one that seems to inflict the biggest performance hit. Any suggestions on how I might boost the performance?
Thanks in advance,
Jorg
]]>