Forum Discussion
DAX Comparison Operations do not support comparing values of type true/false with values of type txt
Hi All,
I am trying to do a large measure to convert unit of measure in a production report. I keep getting the below error message on my dax formula. Can you help??
Error Message:
MdxScript(Model) (20, 9) Calculation error in measure 'BATCH-REC-HDR'[Lbs Manufactured]: DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
Measure =
SUMX (
'BATCH-REC-HDR',
SWITCH (
TRUE (),
'BATCH-REC-HDR'[Qty-Manufactured-UM]="Gal", 'BATCH-REC-HDR'[Quantity-Manufactured]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity],
'BATCH-REC-HDR'[Qty-Manufactured-UM]="Q", 'BATCH-REC-HDR'[Quantity-Manufactured]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity]/4,
'BATCH-REC-HDR'[Qty-Manufactured-UM]="L", 'BATCH-REC-HDR'[Quantity-Manufactured]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity]/3.7853,
'BATCH-REC-HDR'[Qty-Manufactured-UM]="lb", 'BATCH-REC-HDR'[Quantity-Manufactured],
'BATCH-REC-HDR'[Qty-Manufactured-UM]="kg", 'BATCH-REC-HDR'[Quantity-Manufactured]*2.2046,
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="kg", 'BATCH-REC-HDR'[Quantity-Manufactured]*'BATCH-REC-HDR'[PROD-PKG.Weight]*2.2046,
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="lb", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight],
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="Gal", 'BATCH-REC-HDR'[Quantity-Manufactured]*'BATCH-REC-HDR'[PROD-PKG.Weight]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity],
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="Q", 'BATCH-REC-HDR'[Quantity-Manufactured]*'BATCH-REC-HDR'[PROD-PKG.Weight]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity]/4,
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="g", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight]*0.0022046,
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="L", 'BATCH-REC-HDR'[Quantity-Manufactured]*'BATCH-REC-HDR'[PROD-PKG.Weight]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity]/3.7853,
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="lb", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight]*.0625
Anonymous sorry for the late reply, if you want to check AND , it has to be two && instead of single, single & means concatenate values and that's surely a problem, change it to this &&
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
9 Replies
Anonymous very hard to follow your expression but somewhere you are comparing text with the number and you need to check the data type of your columns and fix it accordingly.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Hi parry2k thanks for the response.
Is there a way that I can compare a text column with a number? Here are the columns I am comparing:
Qty Manufactured UM = the unit of measure we manufactured product in, for example: "Gal"
What I want to calculate is if the Qty Manufactured UM = "Gal" then take the quantity manufactured (number field) and multiply this by 8.3333 and by the specific gravity of the product (also a number field).
There are also times where I would like to calculate if the Qty Manufacutured UM = "E" (which stands for Each) AND the product package UM = "Gal" then take the Qty Manufactured (number field) and multiply this by the product package weight (also number field) * 8.3333 * the product specific gravity.
Example of what I wrote that did not work:
Measure =
SUMX (
'BATCH-REC-HDR',
SWITCH (
TRUE (),
'BATCH-REC-HDR'[Qty-Manufactured-UM]="Gal", 'BATCH-REC-HDR'[Quantity-Manufactured]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity],
'BATCH-REC-HDR'[Qty-Manufactured-UM]="E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM]="Gal", 'BATCH-REC-HDR'[Quantity-Manufactured]*'BATCH-REC-HDR'[PROD-PKG.Weight]*8.3333*'BATCH-REC-HDR'[PRODUCT.Specific-Gravity]
Hi Anonymous ,
See if this works.
Measure = VAR val = SWITCH ( TRUE (), 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "Gal", 'BATCH-REC-HDR'[Quantity-Manufactured] * 8.3333 * 'BATCH-REC-HDR'[PRODUCT.Specific-Gravity], 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "Q", 'BATCH-REC-HDR'[Quantity-Manufactured] * 8.3333 * 'BATCH-REC-HDR'[PRODUCT.Specific-Gravity] / 4, 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "L", 'BATCH-REC-HDR'[Quantity-Manufactured] * 8.3333 * 'BATCH-REC-HDR'[PRODUCT.Specific-Gravity] / 3.7853, 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "lb", 'BATCH-REC-HDR'[Quantity-Manufactured], 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "kg", 'BATCH-REC-HDR'[Quantity-Manufactured] * 2.2046, 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM] = "kg", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight] * 2.2046, 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM] = "lb", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight], 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM] = "Gal", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight] * 8.3333 * 'BATCH-REC-HDR'[PRODUCT.Specific-Gravity], 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM] = "Q", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight] * 8.3333 * 'BATCH-REC-HDR'[PRODUCT.Specific-Gravity] / 4, 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM] = "g", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight] * 0.0022046, 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM] = "L", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight] * 8.3333 * 'BATCH-REC-HDR'[PRODUCT.Specific-Gravity] / 3.7853, 'BATCH-REC-HDR'[Qty-Manufactured-UM] = "E" & 'BATCH-REC-HDR'[PROD-PKG.Weight-UM] = "lb", 'BATCH-REC-HDR'[Quantity-Manufactured] * 'BATCH-REC-HDR'[PROD-PKG.Weight] * .0625 ) RETURN SUMX ( BATCH - REC - HDR, val )
Regards,Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)Did I answer your question? Mark my post as a solution!
Hi Anonymous
Thanks for the quick response. Here was the error I recieved when trying this:
A single value for column 'Quantity-Manufactured' in table 'BATCH-REC-HDR' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Anonymous logically it should work, since there are too many conditions, maybe one of it is failing, for debugging, try simple calculation for each condition to see if it all work and maybe you missed something and debug will help to find out the issue.