Forum Discussion
DAX to compare two columns having different data types
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?
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.
3 Replies
- AmiraBedh
Super User
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.
ChiragGarg2512Solution Sage
It's either that [Aggregatiion Code] is text or [Last 13wks Flag] is an integer.
Thanks AmiraBedh! I have converted the tDate [Last 13Wks Flag] to boolean getting easier to compare