cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marck
Helper II
Helper II

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 category],"Call")

then [Total events]

 

ELSEIF [account]="B"

and CONTAINS([Event category],"Form")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call")

then [Total events]


ELSEIF [account]="C"

and CONTAINS([Event category],"Newsletter")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call")

then [Total events]


ELSEIF [account]="D"

and CONTAINS([Event category],"Form")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call")

then [Total events]


ELSEIF [account]= "E"then [all goals]END

 

 

I have tried to work out the possible query in Powerbi. By doing the following:

SWITCH( True() ,
[account] in {"A","B","D"},
IF(CONTAINS('All Data','All Data'[Event category],"Form",
'All Data'[Event category],"Email",'All Data'[Event category],
"Call",'All Data'[Total events]),
 
SWITCH(TRUE(),
[account]in {"C"},
IF(CONTAINS('All Data','All Data'[Event category],"Newsletter",
'All Data'[Event category],"Email",'All Data'[Event category],
"Call",'All Data'[Total events]),
 
SWITCH( True() ,
[account] in {"E"},'All Data'[Goal completion all goals])))
 
However, the query is not showing any value.
Someone can help me in this?
 
Thank you
 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

One final word of advice if I may.  There's a reason why there is Tableau, Qlik, Power BI, etc.  They are all different. They have (sometimes dramatically) different philosophies. Trying to "make Power BI work like Tableau" is not a good approach  (developers know that as "fighting the API").  It is much more important to clearly define the business requirement, and then to implement that in your system of choice, regardless of how it may have been implemented in a previous system.

lbendlin
Super User
Super User

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.

View solution in original post

Thank you for your help @lbendlin 

lbendlin
Super User
Super User

Yes, I think that. As in my example - you have not indicated what you want returned when the account is "F", for example.

 

The DAX IF and SWITCH statements work in both modes

 

IF(A=B,C)  will give C if A equals B, otherwise it will return BLANK().  If you don't want that, specify

IF(A=B,C,D)  which will give C if A equals B, otherwise it will return D.

Thank you, I have added the case for F in your suggested function. Do you think it may be ok?  And can I ask you please what the >0 after each search function is doing?
 
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]),
"F",Blank(),   
"E",[All Goals])

 

lbendlin
Super User
Super User

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])

 

lbendlin_0-1612277291913.png

 

 

 

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?

lbendlin
Super User
Super User

Did you notice that your Tableau expression is likely faulty?  Logical AND overrides logical OR.

So your code

IF [account]="A"

and CONTAINS([Event category],"Form")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call")

then [Total events]

 

is actually equivalent to 

 

IF ([account]="A" and CONTAINS([Event category],"Form"))

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call")

then [Total events]

 

which is most likely not what you wanted. What you wanted might have been

IF [account]="A"

and (CONTAINS([Event category],"Form") or CONTAINS([Event category],"Email") or CONTAINS([Event category],"Call"))

then [Total events]

 

Please clarify.

Hi,

Thank you for your response yes you are right, 

 

The tableau function should have been this:

 

IF [account]="A"

and (CONTAINS([Event category],"Form")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call"))

then [Total events]

 

ELSEIF [account]="B"

and (CONTAINS([Event category],"Form")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call"))

then [Total events]


ELSEIF [account]="C"

and (CONTAINS([Event category],"Newsletter")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call"))

then [Total events]


ELSEIF [account]="D"

and (CONTAINS([Event category],"Form")

or CONTAINS([Event category],"Email")

or CONTAINS([Event category],"Call"))

then [Total events]


ELSEIF [account]= "E"then [all goals]END

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!