How to write an expression using ISBLANK with 3 possible outcomes?

[jdubs]

I want to compare two fields to see if they have data or not and return 3 possible outcomes:

e.g.

Field 1 CONTAINS DATA but Field 2 DOES NOT CONTAIN DATA = True

Field 1 DOES NOT CONTAIN DATA but Field 2 CONTAINS DATA = False

Neither Field 1 nor Field 2 CONTAIN DATA = 3rd option

[jdubs]

Thank you!

[PhilipTreacy]

Hi @jdubs 

Download sample PBIX with code

With 2 fields to check you have 4 possible combinations so you need to specify what the result is if both Field1 and Field 2 have data.

In the following code the case where both fields have data results in the default result.

This code creates a measure.  Its better to use a measure than a calculated column in almost all cases.

Compare Fields = 

VAR _Field1 = SELECTEDVALUE('Table'[Field 1])
VAR _Field2 = SELECTEDVALUE('Table'[Field 2])

RETURN

SWITCH(

    TRUE(),

    AND(NOT(ISBLANK(_Field1)), ISBLANK(_Field2)), "True",

    AND(ISBLANK(_Field1), NOT(ISBLANK(_Field2))), "False",

    AND(ISBLANK(_Field1), ISBLANK(_Field2)), "3rd option",

    "Default"

)

    

Regards

Phil

[jaideepnema · Accepted solution]

Hi @jdubs ,

Create a calculated column like this:

IS Blank Flag = SWITCH(TRUE(),

AND(ISBLANK('Table'[Field 1]),NOT(ISBLANK('Table'[Field 2]))),"Field 2 Available",

AND(NOT(ISBLANK('Table'[Field 1])),ISBLANK('Table'[Field 2])),"Field 1 Available",

AND(ISBLANK('Table'[Field 1]),ISBLANK('Table'[Field 2])),"Both Field Not Available",

AND(NOT(ISBLANK('Table'[Field 1])),NOT(ISBLANK('Table'[Field 2]))),"Both Field Available",BLANK())

This will give you the desired result

Please accept this as a solution if your question has been answered !!

Appeciate a Kudos 😀