Can't display the visual

[adumith]

I have the below info:

Ticket solved per agent

Ticket solved per agent where the Priority field is “Uncategorized”

Full resolution time brackets (Unsorted). This field has brackets instead of just number. The options are:

0-5 hrs

1-7 days

7-30 days

> 30 days

First reply time Brackets. This field has brackets instead of just number. The options are:

0-1 hrs

1-8 hrs

8-24 hrs

> 30 days

No replies

First resolution time Brackets. This field has brackets instead of just number. The options are:

0-5 hrs

1-7 days

7-30 days

> 30 days

SolvedTime(Hrs). This field has numbers

SameDayResolved. This field has values 1 or 0

Based on this I have been able to develop the following formula that in theory is fine but when I try to use the metric in some visualization it is generating this error and I don't know where the problem is.

Formula: 

AgentRanking = 
VAR TotalTicketsSolved = COUNTROWS(zendesk)
VAR TotalTicketsSolvedUncategorized = CALCULATE(COUNTROWS(zendesk), zendesk[Priority] = "Uncategorized")
VAR AvgFullResolutionTime =
    AVERAGEX(
        zendesk,
        SWITCH(
            TRUE(),
            zendesk[Full resolution time brackets (Unsorted)] = "0-5 hrs", 3,
            zendesk[Full resolution time brackets (Unsorted)] = "1-7 days", 10,
            zendesk[Full resolution time brackets (Unsorted)] = "7-30 days", 18,
            zendesk[Full resolution time brackets (Unsorted)] = "> 30 days", 35,
            0
        )
    )
VAR AvgFirstReplyTime =
    AVERAGEX(
        zendesk,
        SWITCH(
            TRUE(),
            zendesk[First reply time brackets] = "0-1 hrs", 0.5,
            zendesk[First reply time brackets] = "1-8 hrs", 4.5,
            zendesk[First reply time brackets] = "8-24 hrs", 16,
            zendesk[First reply time brackets] = "> 30 days", 35,
            zendesk[First reply time brackets] = "No replies", 0,
            0
        )
    )
VAR AvgFirstResolutionTime =
    AVERAGEX(
        zendesk,
        SWITCH(
            TRUE(),
            zendesk[First resolution time brackets] = "0-5 hrs", 3,
            zendesk[First resolution time brackets] = "1-7 days", 10,
            zendesk[First resolution time brackets] = "7-30 days", 18,
            zendesk[First resolution time brackets] = "> 30 days", 35,
            0
        )
    )
VAR AvgSolvedTime = AVERAGE(zendesk[SolvedTime(Hrs)])
VAR SameDayResolvedPercentage = DIVIDE(CALCULATE(COUNTROWS(zendesk), zendesk[SameDayResolved] = 1), COUNTROWS(zendesk))

RETURN
    (VALUE(TotalTicketsSolved) * 0.2)
    + (VALUE(TotalTicketsSolvedUncategorized) * 0.2)
    + (AvgFullResolutionTime * 0.15)
    + (AvgFirstReplyTime * 0.1)
    + (AvgFirstResolutionTime * 0.15)
    + (AvgSolvedTime * 0.1)
    + (SameDayResolvedPercentage * 0.1)

Error:

Couldn’t load the data for this visual.

MdxScript(Model) (219,70) Calculation error in zendesk[AgentRanking]: DAX comparison operations do 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.

Do you think you can look at the formula and see the error, I have not been able to detect it.

Thank you so much in advance, 

[vanessafvg]

its telling you that you have combined string value and number in one of these operations you have above

please share your data types of all the fields you are using, you will need to probably convert the one side

[adumith]

Hello,

Thank you for your reply.

There you go

Full resolution time brackets (Unsorted) is text

First reply time brackets is text

First resolution time brackets is text

SolvedTime(Hrs) is general

SameDayResolved is text

Thank you in advance,

[adumith · Accepted solution]

I was able to solve the problem on my own. I noticed that the formula problem was due to the type of data in the database so I made the corresponding changes and solved it.

In addition to that I have added more variables to the metric to be as objective as possible, I leave them in case you want to use it in the future.

AgentRanking = 
VAR TotalTicketsSolved =
    COUNTROWS(FILTER(zendesk, zendesk[Status] = "Solved"))
VAR TotalTicketsSolvedHighPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "High"
    )
VAR TotalTicketsSolvedLowPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Low"
    )
VAR TotalTicketsSolvedNormalPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Normal"
    )
VAR TotalTicketsSolvedUrgentPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Urgent"
    )
VAR TotalTicketsSolvedUncategorized =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Uncategorized"
    )
VAR AvgFullResolutionTime =
    AVERAGEX(
        FILTER(zendesk, zendesk[Status] = "Solved"),
        SWITCH(
            TRUE(),
            zendesk[Full resolution time brackets (Unsorted)] = "0-5 hrs", 3,
            zendesk[Full resolution time brackets (Unsorted)] = "1-7 days", 10,
            zendesk[Full resolution time brackets (Unsorted)] = "7-30 days", 18,
            zendesk[Full resolution time brackets (Unsorted)] = "> 30 days", 35,
            0
        )
    )
VAR AvgFirstReplyTime =
    AVERAGEX(
        FILTER(zendesk, zendesk[Status] = "Solved"),
        SWITCH(
            TRUE(),
            zendesk[First reply time brackets] = "0-1 hrs", 0.5,
            zendesk[First reply time brackets] = "1-8 hrs", 4.5,
            zendesk[First reply time brackets] = "8-24 hrs", 16,
            zendesk[First reply time brackets] = "> 30 days", 35,
            zendesk[First reply time brackets] = "No replies", 0,
            0
        )
    )
VAR AvgFirstResolutionTime =
    AVERAGEX(
        FILTER(zendesk, zendesk[Status] = "Solved"),
        SWITCH(
            TRUE(),
            zendesk[First resolution time brackets] = "0-5 hrs", 3,
            zendesk[First resolution time brackets] = "1-7 days", 10,
            zendesk[First resolution time brackets] = "7-30 days", 18,
            zendesk[First resolution time brackets] = "> 30 days", 35,
            0
        )
    )
VAR AvgSolvedTime =
    AVERAGEX(FILTER(zendesk, zendesk[Status] = "Solved"), zendesk[SolvedTime(Hrs)])
VAR SameDayResolvedPercentage =
    DIVIDE(
        CALCULATE(
            COUNTROWS(zendesk),
            zendesk[Status] = "Solved",
            zendesk[SameDayResolved] = 1
        ),
        COUNTROWS(FILTER(zendesk, zendesk[Status] = "Solved"))
    )
RETURN
    (VALUE(TotalTicketsSolved) * 0.2)
    + (VALUE(TotalTicketsSolvedUrgentPriority) * 0.2)
	+ (VALUE(TotalTicketsSolvedHighPriority) * 0.15)
    + (VALUE(TotalTicketsSolvedNormalPriority) * 0.1)    
	+ (VALUE(TotalTicketsSolvedLowPriority) * 0.05)
    + (VALUE(TotalTicketsSolvedUncategorized) * 0.02)
    + (AvgFullResolutionTime * 0.15)
    + (AvgFirstReplyTime * 0.1)
    + (AvgFirstResolutionTime * 0.15)
    + (AvgSolvedTime * 0.1)
    + (SameDayResolvedPercentage * 0.1)