Elegant way to exclude values below 0 in a measure?

Questions & Answers
1

Hi all,

I’m trying to find a simple/elegant way to tweak a measure so that it returns null/nothing when a calculation is < 0.

For example, I have a measure that uses the LinRegPoint function to create a line on the chart and I want this line to basically truncate when it reaches 0.

There are two approaches I can think of, neither of which are what I would call elegant, and I’m confident someone here can point me in the right direction if there’s a preferred way of accomplishing this.

Solution #1 - IIf statement

IIf(LinRegPoint(…) > 0, LinRegPoint(…), null)

This will very cumbersome to read as the arguments for LinRegPoint are quite long (spread across several lines) and I worry it is computationally wasteful. Would LinRegPoint(…) get computed 2 times?

Solution #2 - Measure Wrapping

Essentially a version of #1 that is easier to parse…just wrap the initial measure in a new one that has the same IIf logic. Same efficiency concern as I have with #1 but much easier to read.

IIf([Measures].[MyLinRegPointMeasure] >0, [Measures].[MyLinRegPointMeasure, null)

Is there a better solution to accomplish this that I’m just not seeing? Please let me know if you have any ideas. Thx!

2

Hi chrispy35,
For performance, I suggest wrapping your LinReg with the Cache() function, which will calculate your LinRegPoint expression only once and reuse the cached result:

CASE WHEN Cache(LinRegPoint(...)) >= 0  -- Calculates & caches
THEN Cache(LinRegPoint(...))             -- Retrieves from cache
END

For readability and easier maintenance, I think creating “MyCachedLinRegPointMeasure” would be the best approach.

Best,
Ilze // eazyBI support

1 Like