Forum Discussion
DAX formatting error - VALUE or FORMAT function recommended
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
The issue is the "&". A single ampersand like you have here is the string concatenation operator, you want "&&" which is a logical and.
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
4 Replies
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 AzamThank you for your response and the time taken to differentiate the & and the && meaning for me,
Kind Regards
The issue is the "&". A single ampersand like you have here is the string concatenation operator, you want "&&" which is a logical and.
OMG Thank you so much, what a silly omission on my part, thank you for spotting
