DAX to compare two columns having different data types

[Anonymous]

Hi guys,

When using the DAX sentence below:

Week Accuracy (Last 13wks Flag) %:=VAR AggCode = Switch(

True(),

Max(tAccuracy[Aggregatiion Code]) = 2 && MAXA(tDate[Last 13wks Flag]) = "TRUE", [Week Product ID Location Accuracy %],

Max (tAccuracy[Aggregatiion Code]) = 1 && MAXA(tDate[Last 13wks Flag]) = "TRUE", [Week Product Group Location Accuracy %],

Max (tAccuracy[Aggregatiion Code]) = 0 && MAXA(tDate[Last 13wks Flag]) = "TRUE", [Week Product Group Accuracy %]

)

Return

IF ( AggCode > 1, 0, if(AND ( AggCode <= 1, AggCode > 0 ), AggCode, 0 ))

I am getting an error saying that is not possible to compare values of type integer and values of type text. I tried to use VALUE on 

MAXA(VALUE(tDate[Last 13wks Flag])) = "TRUE" but still didn't work. Any suggestion?

[Anonymous]

Thanks @AmiraBedh! I have converted the tDate [Last 13Wks Flag] to boolean getting easier to compare

[AmiraBedh · Accepted solution]

If Last 13wks Flag is stored as text but actually represents a boolean, I think you'll need to properly convert this text to a numeric or boolean type that can be compared correctly. 

Week Accuracy (Last 13wks Flag) %:=VAR AggCode = Switch(
    True(),
    Max(tAccuracy[Aggregation Code]) = 2 && SWITCH(MAXA(tDate[Last 13wks Flag]), "TRUE", 1, "FALSE", 0, 0) = 1, [Week Product ID Location Accuracy %],
    Max(tAccuracy[Aggregation Code]) = 1 && SWITCH(MAXA(tDate[Last 13wks Flag]), "TRUE", 1, "FALSE", 0, 0) = 1, [Week Product Group Location Accuracy %],
    Max(tAccuracy[Aggregation Code]) = 0 && SWITCH(MAXA(tDate[Last 13wks Flag]), "TRUE", 1, "FALSE", 0, 0) = 1, [Week Product Group Accuracy %]
)
Return
IF ( AggCode > 1, 0, IF(AND ( AggCode <= 1, AggCode > 0 ), AggCode, 0 ))

Also I have a doubt that tDate[Last 13wks Flag] is indeed expected to be a boolean . If it’s actually a numeric format where 1 represents true, then the conversion would be different.

[ChiragGarg2512]

It's either that [Aggregatiion Code] is text or [Last 13wks Flag] is an integer.