Forum Discussion

utsavlexmark's avatar
utsavlexmark
Helper III
1 year ago
Solved

Custom Column: Verifying two Conditions (If first not working then second)

Hello All,

I have created a custom column; where two condictions will be verified - if first not working then go to second.

I have declared two "veriables" based on two different columns. 

Email NameVerticals
  

In first condition "variable 1" will look for whether any data is avaiable in verticals column; if found will show the value. If verticals column is blank then second condition will fire and "variable 2" will look for a specific terms in Email Name column and will show the value. If none of the condition works - then it will show "Others".

 

Query is as follows:

Custom Column= 

VAR veriable 1='Table'[Vericals]

VAR veriable 2='Table'[Email Name]

RETURN
SWITCH(TRUE(),
SEARCH("ABC",veriable 1,1,0)>0,"ABC",

SEARCH("DEF",veriable 1,1,0)>0,"DEF",

.

.

.

SEARCH("Term 1",veriable 2,1,0)>0,"ABC",

SEARCH("Term 2",veriable 2,1,0)>0,"DEF",

.

.

.

 

Now for some cases "veriable 1" forund value in "Verticals" columns and return value for a email in Custom Column. But after that "Term 2" found in email name of that particular email and "veriable 2" triggered. Table showing value as:

Email NameVerticalsCustom Column
TestEmail-Term 2ABCABC
TestEmail-Term 2 DEF

 

Although the "TestEmail-Term 2" is mentioned only once in database.

When I am creating a table in power bi it looks like:

Custom ColumnEmail NameDelivery
ABCTestEmail-Term 22
DEFTestEmail-Term 22
Total 2

 

This is the scenario; any suggestion?

Regards

Utsav

Need Help
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    utsavlexmark , Try using updated measure

     

    Custom Column =
    VAR veriable1 = 'Table'[Verticals]
    VAR veriable2 = 'Table'[Email Name]

    RETURN
    IF(
    NOT(ISBLANK(veriable1)),
    SWITCH(TRUE(),
    SEARCH("ABC", veriable1, 1, 0) > 0, "ABC",
    SEARCH("DEF", veriable1, 1, 0) > 0, "DEF",
    -- Add more conditions as needed
    "Others"
    ),
    SWITCH(TRUE(),
    SEARCH("Term 1", veriable2, 1, 0) > 0, "ABC",
    SEARCH("Term 2", veriable2, 1, 0) > 0, "DEF",
    -- Add more conditions as needed
    "Others"
    )
    )

2 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi utsavlexmark ,
    You should prioritize conditions explicitly so that once veriable 1 matches, the veriable 2 logic does not execute. Update your DAX code as follows:

     

    Custom Column = 
VAR variable1 = 'Table'[Verticals]
VAR variable2 = 'Table'[Email Name]

RETURN
SWITCH(
    TRUE(),
    NOT(ISBLANK(variable1)) && SEARCH("ABC", variable1, 1, 0) > 0, "ABC",
    NOT(ISBLANK(variable1)) && SEARCH("DEF", variable1, 1, 0) > 0, "DEF",
    SEARCH("Term 1", variable2, 1, 0) > 0, "ABC",
    SEARCH("Term 2", variable2, 1, 0) > 0, "DEF",
    "Others"
)

     

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    utsavlexmark , Try using updated measure

     

    Custom Column =
    VAR veriable1 = 'Table'[Verticals]
    VAR veriable2 = 'Table'[Email Name]

    RETURN
    IF(
    NOT(ISBLANK(veriable1)),
    SWITCH(TRUE(),
    SEARCH("ABC", veriable1, 1, 0) > 0, "ABC",
    SEARCH("DEF", veriable1, 1, 0) > 0, "DEF",
    -- Add more conditions as needed
    "Others"
    ),
    SWITCH(TRUE(),
    SEARCH("Term 1", veriable2, 1, 0) > 0, "ABC",
    SEARCH("Term 2", veriable2, 1, 0) > 0, "DEF",
    -- Add more conditions as needed
    "Others"
    )
    )