topic DAX formatting error - VALUE or FORMAT function recommended in DAX Commands and Tips

DAX formatting error - VALUE or FORMAT function recommended

SHRJ247 — Wed, 18 Jun 2025 12:47:41 GMT

I need help either effectively applying the FORMAT or VALUE commands vs something else if I have misunderstood the formatting error I have recieved on my DAX code (copied below)

Error recieved =

"DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values"

The formula excutes successfully if I remove the "DOP < 42" component of the Return, yet this column of the table is formatted as a whole number, as is the "Reporting Year" - is it because I am asking if AED_RY and S_RY (whole number values derived from a date) are sourced from a Date column?

S_RY = a whole number reporting year extracted from a date column

AED_ RY = a whole number reporting year extracted from a date column

DOP = a whole number unrelated to a date and when removed from the IF enables the IF to work - i believe this is therefore the source of the issue, but this is still a key component of the overall DAX code

Any help very greatly appreciated, thank you

Re: DAX formatting error - VALUE or FORMAT function recommended

johnt75 — Wed, 18 Jun 2025 13:03:31 GMT

The issue is the "&". A single ampersand like you have here is the string concatenation operator, you want "&&" which is a logical and.

Re: DAX formatting error - VALUE or FORMAT function recommended

SHRJ247 — Wed, 18 Jun 2025 13:34:33 GMT

OMG Thank you so much, what a silly omission on my part, thank you for spotting

Re: DAX formatting error - VALUE or FORMAT function recommended

Nasif_Azam — Wed, 18 Jun 2025 14:01:32 GMT

Hey SHRJ247 ,

You're getting this error: “DAX comparison operations do not support comparing values of type Integer with values of type Text…”

This occurs because of the & operator, which concatenates text it does not function as a logical AND (&&). DAX interprets S_RY & DOP < 42 as text concatenation (even though they’re numbers), and the resulting expression becomes semantically invalid.

You likely meant to use the logical AND operator &&. So change this line:

IF (AED_RY > S_RY & DOP < 42, S_RY, AED_RY)

To:

IF (AED_RY > S_RY && DOP < 42, S_RY, AED_RY)

Final Corrected Code

Draft =
VAR S_RY = LOOKUPVALUE('Aims Calender'[Reporting Year], 'Aims Calender'[Date], Aims[StartDate])
VAR AED_RY = RELATED('Aims Calender'[Reporting Year])
VAR DOP = Aims[Days On Programme]
RETURN
IF (AED_RY > S_RY && DOP < 42, S_RY, AED_RY)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

Re: DAX formatting error - VALUE or FORMAT function recommended

SHRJ247 — Thu, 19 Jun 2025 08:28:49 GMT

Thank you for your response and the time taken to differentiate the & and the && meaning for me,

Kind Regards