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

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 III
Super User III

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 III
Super User III

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 III
Super User III

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 III
Super User III

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 III
Super User III

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 III
Super User III

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors