Hi guys.
How do I create a calculated field with the following rule?
if ‘hours spent’ is filled → guide line = hours remaining - hours spent
else → guide line = remaining hours - average of hours spent
Thanks.
Hi @raphalopes,
There are several ways of using conditions within the calculated measures. I will describe the three most popular that relate to the specific case.
Since you did not specify what average you require, I refer to a generic “measure” named “Average spent hours”.
The CoalesceEmpty function is helpful for defining a new value if the initially requested field returns empty.
The expression for the calculated measure “guide line” might be as follows.
[Measures].[Hours remaining]
-
CoalesceEmpty(
--the original measure
[Measures].[Hours spent],
--the alternative measure if the original was empty
[Measures].[Average spent hours]
)
The IIF construction is applicable when there are only two possible scenarios for the action.
In this case, you might use the function IsEmpty to check if the “Hours spent” returns a result.
The expression for the calculated measure “guide line” might be as follows.
[Measures].[Hours remaining]
-
IIF(
--condition
NOT IsEmpty([Measures].[Hours spent]),
--the original measure
[Measures].[Hours spent],
--the alternative measure
[Measures].[Average spent hours]
)
CASE WHEN statement
The CASE construction allows setting the conditions for (multiple) different scenarios depending on the (multiple) conditions.
The expression for the calculated measure “guide line” might be as follows.
CASE WHEN
--condition
IsEmpty([Measures].[Hours spent])
THEN
--one course of action
[Measures].[Hours remaining]
-
[Measures].[Average spent hours]
ELSE
--another course of action
[Measures].[Hours remaining]
-
[Measures].[Hours spent]
END
Feel free to use the option you like best.
If you need assistance creating the MDX expression for the average hour spent - please write to the support e-mail with more details about the business logic behind this figure and the report context where it will be used.
Regards,
Oskars / support@eazyBI.com