Help with IF Function

[heidibb]

Hello,

I am trying to derrive a "due date" field depending on the value in my WEEK column. I am getting an error with this stating "DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT funciton to convert on of the values."

I understand what the error is telling me, but I'm not sure how to modify my formula to accomodate. 

Thoughts?

Assessment Due Date = if(WeeklyAssessment[WEEK] = 1,DATEADD(WeeklyAssessment[SectionStartDate],2,DAY),
							if(WeeklyAssessment[WEEK] = 2, dateadd(WeeklyAssessment[SectionStartDate],9,DAY),
								if(WeeklyAssessment[WEEK] = 3, dateadd(WeeklyAssessment[SectionStartDate],16,DAY),
									if(WeeklyAssessment[WEEK] = 4, dateadd(WeeklyAssessment[SectionStartDate],23,DAY),
										if(WeeklyAssessment[WEEK] = 5, dateadd(WeeklyAssessment[SectionStartDate],30,DAY),
											if(WeeklyAssessment[WEEK] = 6, dateadd(WeeklyAssessment[SectionStartDate],37,DAY),
												if(WeeklyAssessment[WEEK] = 7, dateadd(WeeklyAssessment[SectionStartDate],44,DAY),
													if(WeeklyAssessment[WEEK] = 8, dateadd(WeeklyAssessment[SectionStartDate],51,DAY),
														if(WeeklyAssessment[WEEK] = 9, dateadd(WeeklyAssessment[SectionStartDate],58,DAY),
															if(WeeklyAssessment[WEEK] = 10, dateadd(WeeklyAssessment[SectionStartDate],65,DAY),today()))))))))))

[Anonymous · Accepted solution]

Have a look in the Modelling section of your Ribbon after you have selected your column "WEEK".  Check that this column is set to being a Whole Number.  The error is indicating that the field is set to TEXT.  If you need it to be set to text, simply change your comparisions in your DAX statement to be ="1".

As a side note, a Switch statement would work better for your expression.  Here is how it would look:

Assessment Due Date = SWITCH(
	WeeklyAssessment[WEEK],
	1, DATEADD(WeeklyAssessment[SectionStartDate],2,DAY),
	2, DATEADD(WeeklyAssessment[SectionStartDate],9,DAY),
	3, DATEADD(WeeklyAssessment[SectionStartDate],16,DAY),
	4, DATEADD(WeeklyAssessment[SectionStartDate],23,DAY),
	5, DATEADD(WeeklyAssessment[SectionStartDate],30,DAY),
	6, DATEADD(WeeklyAssessment[SectionStartDate],37,DAY),
	7, DATEADD(WeeklyAssessment[SectionStartDate],44,DAY),
	8, DATEADD(WeeklyAssessment[SectionStartDate],51,DAY),
	9, DATEADD(WeeklyAssessment[SectionStartDate],58,DAY),
	10, DATEADD(WeeklyAssessment[SectionStartDate],65,DAY),
	TODAY()
)

[heidibb]

Thank you!

I did change the data type to whole number and my error went away making it look like the formula worked, but for some reason I was getting no value in my result set. I realized it doesn't like the DATEADD section as I did a test replacing that with a zero and I did get the result of zero.

So, I changed the DATEADD portion to just SectionStartDate+2 and that worked. So strange why dateadd did not work.

Thank you for your help!