cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickPatelMRCG Regular Visitor
Regular Visitor

'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

This is a huge DAX that works except for one line. We are compaing Text only. I'm a unsure why I get this error. The ultimate Goal is to obtain the correct manager of all the teams names from the back end data.(Such as "Apps North America") This is data from our ticketing system.  The one section causes this issue. I'll forward the PBIX File if needed.

 

ERROR: Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

 

DAX:

 

Manager =
VAR B1 =
CALCULATE (
LOOKUPVALUE (
'Home Team Owners'[Owner],
'Home Team Owners'[Hometeam], SELECTEDVALUE ( 'IS Ticket Metrics - All'[Home Team] )
),
USERELATIONSHIP ( 'Home Team Owners'[Hometeam], 'IS Ticket Metrics - All'[Home Team] )
)
VAR B2 =
CALCULATE (
LOOKUPVALUE (
'Home Team Owners (2)'[Owner],
'Home Team Owners (2)'[Hometeam], SELECTEDVALUE ( 'IS Ticket Metrics - All'[Team: Name] )
),
USERELATIONSHIP ( 'Home Team Owners (2)'[Hometeam], 'IS Ticket Metrics - All'[Team: Name] )
)
VAR B3 =
CALCULATE (
LOOKUPVALUE (
'Home Team Owners (3)'[Owner],
'Home Team Owners (3)'[Hometeam], SELECTEDVALUE ( 'IS Ticket Metrics - All'[Assignments.1] )
),
USERELATIONSHIP ( 'Home Team Owners (3)'[Hometeam], 'IS Ticket Metrics - All'[Assignments.1] )
)
VAR B4 =
CALCULATE (
LOOKUPVALUE (
'Home Team Owners (4)'[Owner],
'Home Team Owners (4)'[Hometeam], SELECTEDVALUE ( 'IS Ticket Metrics - All'[Assignments_1.1] )
),
USERELATIONSHIP ( 'Home Team Owners (4)'[Hometeam], 'IS Ticket Metrics - All'[Assignments_1.1] )
)
VAR B5 =
CALCULATE (
IF (
SELECTEDVALUE ( 'IS Ticket Metrics - All'[Home Team] ) = "Apps North America",
LOOKUPVALUE (
'Team Owners'[Owner],
'Team Owners'[Owner], SELECTEDVALUE ( 'IS Ticket Metrics - All'[Assignments.1] )
)
),
USERELATIONSHIP ( 'Team Owners'[Owner], 'IS Ticket Metrics - All'[Assignments.1] )
)
VAR B6 =
TRIM ( SELECTEDVALUE ( 'IS Ticket Metrics - All'[Assignments.2] ) )
VAR B7 =
CALCULATE (
IF (
SELECTEDVALUE ( 'IS Ticket Metrics - All'[Home Team] ) = "Apps North America",
LOOKUPVALUE ( 'Team Owners (2)'[Owner], 'Team Owners (2)'[Owner], B6 )
),
USERELATIONSHIP ( 'Team Owners (2)'[Owner], 'IS Ticket Metrics - All'[Assignments.2] )
)
VAR B8 =
TRIM ( SELECTEDVALUE ( 'IS Ticket Metrics - All'[Assignments_1.2] ) )
VAR B9 =
CALCULATE (
B8
& LOOKUPVALUE (
'Home Team Owners (5)'[Owner],
'Home Team Owners (5)'[Hometeam], B8
),
USERELATIONSHIP ( 'Home Team Owners (5)'[Hometeam], 'IS Ticket Metrics - All'[Assignments_1.2] )
)
VAR B10 =
CALCULATE (
LOOKUPVALUE (
'Home Team Owners (5)'[Owner],
'Home Team Owners (5)'[Hometeam], B7
)
)
VAR B11 =
CALCULATE (
IF (
B10 = BLANK ()
&& B9 = BLANK ()
&& B8 = BLANK ()
&& B7 = BLANK ()
&& B6 = BLANK ()
&& B5 = BLANK ()
&& B4 = BLANK ()
&& b3 = BLANK ()
&& b2 = BLANK ()
&& B1 = BLANK (),
LOOKUPVALUE (
Unassigned1[Manager],
Unassigned1[Ref #], SELECTEDVALUE ( 'IS Ticket Metrics - All'[Ref #] )
),
"Unasigned"
),
USERELATIONSHIP ( Unassigned1[Ref #], 'IS Ticket Metrics - All'[Ref #] )
)
VAR B12 =
CALCULATE (
IF (
B10 = "Marvin Taylor"
|| B9 = "Marvin Taylor"
|| B7 = "Marvin Taylor"
|| B5 = "Marvin Taylor"
|| B4 = "Marvin Taylor"
|| b3 = "Marvin Taylor"
|| b2 = "Marvin Taylor"
|| B1 = "Marvin Taylor"
&& SELECTEDVALUE ( 'IS Ticket Metrics - All'[Home Team] ) = "Apps North America",
LOOKUPVALUE (
'APPS NA'[Manager],
'APPS NA'[Manager], SELECTEDVALUE ( 'IS Ticket Metrics - All'[Ref #] )
)
)
)
VAR B13 =
CALCULATE ( IF ( B7 = BLANK () && B5 = BLANK () && B1 = BLANK (), B10 ) )
VAR B14 =
CALCULATE (
IF ( B7 = BLANK () && B5 = BLANK () && B2 = BLANK () && B1 = BLANK (), B3 )
)
VAR B15 =
CALCULATE ( IF ( B7 = BLANK () && b5 = BLANK () && b1 = BLANK (), B2 ) )
VAR B16 =
CALCULATE ( IF ( B7 = BLANK () && B5 = BLANK (), B1 ) )
VAR B17 =
CALCULATE ( IF ( B5 = BLANK (), B7 ) )
VAR B18 =
CALCULATE (
IF (
B7 = BLANK ()
&& B1 = BLANK ()
&& b2 = BLANK ()
&& b3 = BLANK ()
&& b5 = BLANK (),
B4
)
)
VAR B19 =
CONCATENATE ( B5, B9 )
VAR B20 =
CONCATENATE ( B10, B11 )
VAR B21 =
CONCATENATE ( B12, B13 )
VAR B22 =
CONCATENATE ( B14, B15 )
VAR B23 =
CONCATENATE ( B16, B17 )
VAR B24 =
CONCATENATE ( B18, B19 )
VAR B25 =
CONCATENATE ( B20, B21 )
VAR B26 =
CONCATENATE ( B22, B23 )
VAR B27 =
CONCATENATE ( B24, B25 )
VAR B28 =
CONCATENATE ( B26, B27 )
RETURN
IF ( B28 = BLANK (), "UNASSIGNED", B28 )

 

Any help is appreciated

Thank you,

Nick

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

Hi @NickPatelMRCG

 

If you can send me a PBIX file (strip it down if you need).  Feel free to DM me a link.  This may be a lot faster than going back and forward here 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

Hi @NickPatelMRCG

 

What is the datatype of the 'IS Ticket Metrics - All'[Ref #]  field?

 

  

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

NickPatelMRCG Regular Visitor
Regular Visitor

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

That is the All file extract from the ticketing system and the REF#'s for those tickets.  They are a Number.

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

But what data type is it?  I'm guessing it is a number (whole, decimal or fixed decimal)?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

NickPatelMRCG Regular Visitor
Regular Visitor

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

Whole number

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

 

Please try this small tweak.

 

 

VAR B12 =
CALCULATE (
IF (
B10 = "Marvin Taylor"
|| B9 = "Marvin Taylor"
|| B7 = "Marvin Taylor"
|| B5 = "Marvin Taylor"
|| B4 = "Marvin Taylor"
|| b3 = "Marvin Taylor"
|| b2 = "Marvin Taylor"
|| B1 = "Marvin Taylor"
&& SELECTEDVALUE ( 'IS Ticket Metrics - All'[Home Team] ) = "Apps North America",
LOOKUPVALUE (
'APPS NA'[Manager],
'APPS NA'[Manager], FORMAT(SELECTEDVALUE ( 'IS Ticket Metrics - All'[Ref #] ) ,"")
)
)
)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

NickPatelMRCG Regular Visitor
Regular Visitor

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

No success unfortunately. i'd be happy to forward you the PBIX for this.

Re: 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

Hi @NickPatelMRCG

 

If you can send me a PBIX file (strip it down if you need).  Feel free to DM me a link.  This may be a lot faster than going back and forward here 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)