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 but Field 2 CONTAINS DATA = False

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

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

3 Replies

  • jaideepnema's avatar
    jaideepnema
    Solution Sage
    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