Forum Discussion

jdubs's avatar
jdubs
Helper V
5 years ago
Solved

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

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...
Need Help
  • jaideepnema's avatar
    jaideepnema
    5 years ago

    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 ğŸ˜€

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

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