Z-Score calculation error / data type mismatch

[AC23VM]

Hi there.  I'm trying to work out some z-scores, but Copilot seems stuck in a loop.  I'm getting an error, and Copilot keeps bouncing between 2 suggestions, neither of which work, and appears stuck.  Here is a column, and 3 measures:

Grading_Numerical_Column = 
SWITCH(
    TRUE(),
    'Outcomes Testing Data'[Overall Journey Grading A-E] = "A. No Harm", 1,
    'Outcomes Testing Data'[Overall Journey Grading A-E] = "B. No Harm - Observations", 2,
    'Outcomes Testing Data'[Overall Journey Grading A-E] = "C. Potential Harm B", 3,
    'Outcomes Testing Data'[Overall Journey Grading A-E] = "D. Potential Harm A", 4,
    'Outcomes Testing Data'[Overall Journey Grading A-E] = "E. Actual Harm", 5,
    BLANK()
)


Mean_Harm_Vulnerable = 
CALCULATE(
    AVERAGE('Outcomes Testing Data'[Grading_Numerical_Column]),
    FILTER(
        'Outcomes Testing Data',
        'Outcomes Testing Data'[VC - Yes] = "Yes" && 
        'Outcomes Testing Data'[Overall Journey Grading A-E] IN {"C. Potential Harm B", "D. Potential Harm A", "E. Actual Harm"}
    )
)

StdDev_Harm_Vulnerable = 
CALCULATE(
    STDEV.P('Outcomes Testing Data'[Grading_Numerical_Column]),
    FILTER(
        'Outcomes Testing Data',
        'Outcomes Testing Data'[VC - Yes] = "Yes" && 
        'Outcomes Testing Data'[Overall Journey Grading A-E] IN {"C. Potential Harm B", "D. Potential Harm A", "E. Actual Harm"}
    )
)


Z_Score_Harm_Vulnerable = 
VAR MeanValue = [Mean_Harm_Vulnerable]
VAR StdDevValue = [StdDev_Harm_Vulnerable]
VAR GradingValue = MAX('Outcomes Testing Data'[Grading_Numerical_Column])
RETURN
IF(
    ISBLANK(GradingValue) || StdDevValue = 0,
    BLANK(),
    (GradingValue - MeanValue) / StdDevValue
 

 
The Grading_Numerical_Column is definitely numerical data. The 3 measures thereafter produce the error 'DAX comparison operations do not support comparing values of type Integer with type Text.'  Can anyone read where the error is here because Copilot can't seem to get out of this loop.

[v-ssriganesh]

Hi @AC23VM,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[AC23VM · Accepted solution]

I have.  It did not.  I just did the whole thing again myself.  This subject can be closed now.

[v-ssriganesh]

Hi @AC23VM ,

We greatly appreciate your efforts and thank you for providing the update on the issue. If the issue has been resolved, kindly share the insights, and "Accept it as a solution" so other community members can resolve similar issues more efficiently.

Thank you.

[v-ssriganesh]

Hi @AC23VM,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @AC23VM,
Thank you for reaching out to the Microsoft Fabric Forum Community.

The issue is caused by a data type mismatch somewhere in the calculation, an integer is being compared to a text value, which leads to the error. The DAX comparison operations do not support comparing values of type Integer with type Text.

 To check the actual data type of Grading_Numerical_Column, try this measure:

DataTypeCheck = 
VAR Check = ISNUMBER(MAX('Outcomes Testing Data'[Grading_Numerical_Column]))
RETURN Check

If this returns FALSE, it means Grading_Numerical_Column contains text values instead of numbers. If the issue persists, try to Modify the Grading_Numerical_Column calculation to explicitly return an integer and ensure the column is stored as a Whole Number in Power BI.

If this helps, then please "Accept it as a solution" and dropping a "Kudos" so other members can find it more easily.
Hope this works for you!
Thanks.

[v-ssriganesh]

Hi @AC23VM,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @AmiraBedh for sharing valuable insights.

Could you please confirm if the provided solution has resolved your issue? If so, kindly mark it as the “Accepted solution” to help other community members facing similar challenges find the answer more quickly.
Thank you.

[AC23VM]

Thank you @AmiraBedh. That now gives values rather than blanks. However, I wonder if you could please help me with something else. The Grading Numerical Column was to assign numbers to the gradings, because they're text and couldn't be counted. I'm only interested in the volumes of the gradings, though, the values of the numbers shouldn't be considered. I'm a bit concerned Copilot has suggested formulae where the values are being used in calculations. If I enter the numbers manually into Copilot, it seems to think the z-score for vulnerable customers should be -1.001, but according to this DAX it's 3.98.

For reference, the formulae now all look like this:

VC_Yes_Flag =

IF(

'Outcomes Testing Data'[Vulnerable Customer] = "Vulnerable Customer",

1,

0

)

Mean_Harm_Vulnerable =

CALCULATE(

AVERAGE('Outcomes Testing Data'[Grading_Numerical_Column]),

FILTER(

'Outcomes Testing Data',

'Outcomes Testing Data'[VC_Yes_Flag] = 1 &&

'Outcomes Testing Data'[Overall Journey Grading A-E] IN {

"C. Potential Harm B",

"D. Potential Harm A",

"E. Actual Harm"

}

)

)

StdDev_Harm_Vulnerable =

CALCULATE(

    STDEV.P('Outcomes Testing Data'[Grading_Numerical_Column]),

    FILTER(

        'Outcomes Testing Data',

        'Outcomes Testing Data'[VC_Yes_Flag] = 1 &&

        'Outcomes Testing Data'[Overall Journey Grading A-E] IN {"C. Potential Harm B", "D. Potential Harm A", "E. Actual Harm"}

    )

)

Z_Score_Harm_Vulnerable =

VAR MeanValue = [Mean_Harm_Vulnerable]

VAR StdDevValue = [StdDev_Harm_Vulnerable]

VAR GradingValue = MAX('Outcomes Testing Data'[Grading_Numerical_Column])

RETURN

IF(

    ISBLANK(GradingValue) || StdDevValue = 0,

    BLANK(),

    (GradingValue - MeanValue) / StdDevValue

)

[AmiraBedh]

When reading your code, I have a doubt about 2 columns.

Outcomes Testing Data'[VC - Yes] = "Yes", maybe it’s a Boolean (TRUE/FALSE) or another data type that looks like "Yes"/"No" in the table but isn’t actually stored as a string.
So you need to use TRUE() Instead of Yes :

'Outcomes Testing Data'[VC - Yes] = TRUE()

Or if it's a boolean column shown as “Yes”/“No” but really stores 1/0 or TRUE/FALSE, you could also try:

'Outcomes Testing Data'[VC - Yes] = 1

The second one is 'Overall Journey Grading A-E', you're using string comparisons like:

'Outcomes Testing Data'[Overall Journey Grading A-E] IN {
"C. Potential Harm B",
"D. Potential Harm A",
"E. Actual Harm"
}

This part is fine as long as that column is a text column.
If somehow that column was encoded differently or has trailing spaces or hidden characters, the comparison could break silently.

So, try wrapping it with TRIM():

TRIM('Outcomes Testing Data'[Overall Journey Grading A-E])

Also, try to verify if your Grading_Numerical_Column column is actually returning numbers, not strings "1", "2" ... That would look like numbers but act like text.

You can fix this by explicitly converting to integer:

Grading_Numerical_Column =
SWITCH(
TRUE(),
'Outcomes Testing Data'[Overall Journey Grading A-E] = "A. No Harm", 1,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "B. No Harm - Observations", 2,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "C. Potential Harm B", 3,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "D. Potential Harm A", 4,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "E. Actual Harm", 5,
BLANK()
)

If it's still being treated as text, you can force conversion:

Grading_Numerical_Column =
VALUE(
SWITCH(
TRUE(),
'Outcomes Testing Data'[Overall Journey Grading A-E] = "A. No Harm", "1",
'Outcomes Testing Data'[Overall Journey Grading A-E] = "B. No Harm - Observations", "2",
'Outcomes Testing Data'[Overall Journey Grading A-E] = "C. Potential Harm B", "3",
'Outcomes Testing Data'[Overall Journey Grading A-E] = "D. Potential Harm A", "4",
'Outcomes Testing Data'[Overall Journey Grading A-E] = "E. Actual Harm", "5",
BLANK()
)
)

[AC23VM]

Thanks @AmiraBedh 

The VC - Yes measure is: 

VC - Yes = CALCULATE([Count of Overall Journey Grading],'Outcomes Testing Data'[Vulnerable Customer] IN {"Vulnerable Customer"}) +0

I changed to:

Mean_Harm_Vulnerable =

CALCULATE(

    AVERAGE('Outcomes Testing Data'[Grading_Numerical_Column]),

    FILTER(

        'Outcomes Testing Data',

        'Outcomes Testing Data'[VC - Yes] = "1" &&

        'Outcomes Testing Data'[Overall Journey Grading A-E] IN {"C. Potential Harm B", "D. Potential Harm A", "E. Actual Harm"}

    )

)

But the calculation error is the same.  The Overall Journey Grading A-E is definitely text format, hence making that numerical ranking thing at the top.

[AmiraBedh]

Now it's clearer !
You're using a measure (VC - Yes) in a row context filter like this:

'Outcomes Testing Data'[VC - Yes] = "1"

But [VC - Yes] is a measure, not a column and DAX can't compare a measure in a row-level filter context like that so it leads to a data type mismatch or logic failure because measures don’t exist at the row level in this way.

That’s what’s blowing up your filter logic not the types of your column values.

Your calculated column should be like below :

VC_Yes_Flag =
IF(
'Outcomes Testing Data'[Vulnerable Customer] = "Vulnerable Customer",
1,
0
)

Now use VC_Yes_Flag = 1 in your filter instead of referencing the measure.

Your measure should be like below:

Mean_Harm_Vulnerable =
CALCULATE(
AVERAGE('Outcomes Testing Data'[Grading_Numerical_Column]),
FILTER(
'Outcomes Testing Data',
'Outcomes Testing Data'[VC_Yes_Flag] = 1 &&
'Outcomes Testing Data'[Overall Journey Grading A-E] IN {
"C. Potential Harm B",
"D. Potential Harm A",
"E. Actual Harm"
}
)
)