IF/THEN/ELSE with Contains Function

[t0mato]

Hello!

Please take a look at my formula:

IF (
[Opportunity Title] contains "Full-Time")
THEN (
[Remaining FTE]*1)
ELSE (
[Remaining FTE]/0.5)

In my report, I have different Opportunity Titles that start with either Full-Time or Part-Time. Next to each opportunity title, I have the opportunity's Remaining FTE count - this can be a whole number or decimal.

Based on the formula above, I want BI to look at all Opportunity Titles and if one has "Full-Time" in it, multiply it's corresponding Remaining FTE by 1. If the opportunity title does not have "Full-Time" in it, i.e. "Part-Time", divide that Remaining FTE by 0.5.

It seems straightforward enough but when validating this formula, I am given an error.

Any ideas or suggestions would be greatly appreciated!

EDIT: Problem solved. Double quotes " does not work in BI, should have been single quotes '.

[MFGF]

Hello!

Please take a look at my formula:

IF (
[Opportunity Title] contains "Full-Time")
THEN (
[Remaining FTE]*1)
ELSE (
[Remaining FTE]/0.5)

In my report, I have different Opportunity Titles that start with either Full-Time or Part-Time. Next to each opportunity title, I have the opportunity's Remaining FTE count - this can be a whole number or decimal.

Based on the formula above, I want BI to look at all Opportunity Titles and if one has "Full-Time" in it, multiply it's corresponding Remaining FTE by 1. If the opportunity title does not have "Full-Time" in it, i.e. "Part-Time", divide that Remaining FTE by 0.5.

It seems straightforward enough but when validating this formula, I am given an error.

Any ideas or suggestions would be greatly appreciated!

EDIT: Problem solved. Double quotes " does not work in BI, should have been single quotes '.

Hi,

Yes, you need single quotes around 'Full-Time' in the expression. I have a couple of questions about your logic here, though. Firstly, why multiply by 1? What does this provide that simply using the [Remaining FTE] value alone wouldn't? Secondly, do you really mean to divide by 0.5? This is the exact same thing as multiplying by 2, so why not just multiply by 2? Or do you mean to divide by 2?

Cheers!

MF.