"Text" in formula in PowerPivot

[nielf]

Hi,

I'm trying to merge two date columns into one depending on status.

When the status in column A is either "Awarded" or "Closed" the date from colum B will be used. When the status in column A is "Lost" the date from column C will be used.

I have created the formula in Excel and it works as supposed.

=IF(A2="Awarded";B2;IF(A2="Closed";B2;IF(A2="Lost";C2;"")))

However, now I would like to use the same formula in PowerPivot but it seems that it isn't possible as I get the error below:

My formula in PowerPivot looks like this:

=IF(Data[StatusToBe]="Awarded",Data[Awarded Date],IF(Data[StatusToBe]="Closed",Data[Awarded Date],IF(Data[StatusToBe]="Lost",Data[Status Change Date],"")))

Is it not allowed to refer to "text" in PowerPivot formulas? If so, is there any other way I can re-create the formula in PowerPivot to get it working?

 

[Matt Allington]

I think the issue is that the results of an if statement must always be of the same type. The first 2 return a date, and the third returns a text. Try replacing the third returned value "" with BLANK()

 

[nielf]

I think the issue is that the results of an if statement must always be of the same type. The first 2 return a date, and the third returns a text. Try replacing the third returned value "" with BLANK()

You are right, Matt. Thank you very much for the reply. It's very much appreciated!

 

[Matt Allington]

The last value is optional and the default is blank. So you can also omit the last comma and the blank() function, and it should still work.

 

[nielf]

The last value is optional and the default is blank. So you can also omit the last comma and the blank() function, and it should still work.

That's even better. Thanks.