Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to translate Tableau conditional functions that use also contains function into Power Bi?

Hello,   I would like to create the following Tableau function into Powerbi.   IF [account]="A" and CONTAINS([Event category],"Form") or CONTAINS([Event category],"Email") or CONTAINS([Event c...
Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    5 years ago

    SEARCH("Form",[Event Category],,0)>0

     

    means "look for the string 'Form' in the field [Event Category], ignoring the collation. The search is starting from the first character.  If the substring is found, its starting position is returned (first character is at 1).  If the string is not found then 0 is returned.

     

    Search (something) > 0   is a boolean check that returns true if the substring was found, and false otherwise. This is based on the previous decision to equate "not found" with 0.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Here is the DAX equivalent, using a Calculated Column (not a measure!).

Note that you are missing the "Or Else"  option for scenarios where the Account is not in A..E or the secondary criteria is not met. Rethink your logic again.

 

Result =
var f1= SEARCH("Form",[Event Category],,0)>0 || SEARCH("Email",[Event Category],,0)>0 || SEARCH("Call",[Event Category],,0)>0
var f2= SEARCH("Newsletter",[Event Category],,0)>0 || SEARCH("Email",[Event Category],,0)>0 || SEARCH("Call",[Event Category],,0)>0
return SWITCH(Events[account],
"A",if(f1,[Total Events]),
"B",if(f1,[Total Events]),
"C",if(f2,[Total Events]),
"D",if(f1,[Total Events]),
"E",[All Goals])

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for your help i am not sure what do you mean with Or Else"  option for scenarios where the Account is not in A..E". Do you think I need to add an or statment?