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
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
Solved! Go to 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 🙂
Hi @Anonymous
What is the datatype of the 'IS Ticket Metrics - All'[Ref #] field?
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)?
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 #] ) ,"") ) ) )
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 🙂
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
159 | |
136 | |
133 | |
81 | |
61 |