Custom Column - Multiple Ifs Leaving Blanks?

[Thigs]

Hey all,

I'm trying to do a custom column to see what items will fit into my standard size box. Here is my DAX - 

Box Fit = if('Items'[UNIT_HEIGHT (CM)] < 5,

if('Items'[UNIT_LENGTH(CM)] < 23,

if('Items'[UNIT_WIDTH (CM)] < 20 ,

if('Items'[UNIT_WEIGHT (KG)]< 1,

"FIT", "NO FIT"))))

For some reason, this is leaving the majority of my rows blank. Any ideas? I'm getting a couple hundred items each labeled either "Fit" or "No Fit" but the rest are all blank. I did check, all the items have all those dimensions included - none of them have a blank.

[AlexisOlson · Accepted solution]

This is because you're returning a blank when it doesn't satisfy the first 3 conditions.

You can add those cases back in like this:

Box Fit =
IF (
    'Items'[UNIT_HEIGHT (CM)] < 5,
    IF (
        'Items'[UNIT_LENGTH(CM)] < 23,
        IF (
            'Items'[UNIT_WIDTH (CM)] < 20,
            IF ( 'Items'[UNIT_WEIGHT (KG)] < 1, "FIT", "NO FIT" ),
            "NO FIT"
        ),
        "NO FIT"
    ),
    "NO FIT"
)

or simplify to this:

Box Fit =
IF (
    'Items'[UNIT_HEIGHT (CM)] < 5
        && 'Items'[UNIT_LENGTH(CM)] < 23
        && 'Items'[UNIT_WIDTH (CM)] < 20
        && 'Items'[UNIT_WEIGHT (KG)] < 1,
    "FIT",
    "NO FIT"
)

[Thigs]

Thank you so much! I had tried with just an "and" between them but it looks like it needed two of them. Works perfectly now!

[AlexisOlson]

Yep. A single "&" is used for concatenating text and "&&" is the logical operator.