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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

'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

Hi @Anonymous

 

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
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

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!

Anonymous
Not applicable

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

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!

Anonymous
Not applicable

Whole number

 

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!

Anonymous
Not applicable

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

Hi @Anonymous

 

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!

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.