Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SHRJ247
New Member

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"

 

SHRJ247_0-1750250790297.png

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

 

2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

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

View solution in original post

Nasif_Azam
Super User
Super User

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

View solution in original post

4 REPLIES 4
Nasif_Azam
Super User
Super User

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

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

 

Kind Regards

SHRJ247
New Member

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

johnt75
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.