Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

 

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.