DAX Comparison Operations do not support comparing values of type true/false with values of type txt

[Anonymous]

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

[harshnathani]

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!

[Anonymous]

Hi @harshnathani 

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.

[parry2k]

@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.

[Anonymous]

Thanks @parry2k  - I was able to narrow down what is giving the error. 

It is when I try to write the statement below in bold. Do you think it has something to do with the & sign? I just want it to look at the quantity manufactured UM and if it is "E" and the Prod-Pkg Weight UM is "kg" then I want it to calculate the value (which is not the error portion. 

'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,

[Anonymous]

@Anonymous 

@parry2khas pointed out the cause of the error. "&" is a sign that connects, or concatenates, two values to produce one continuous text value, whereas "&&" is the AND condition you need, change it to double ampersand "&&" should work. 

Best Regards

Paul Zheng _ Community Support Team

[parry2k · Accepted solution]

@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.⚡

[Anonymous]

@parry2k  you are a lifesaver. 

[parry2k]

@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.⚡

[Anonymous]

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]