Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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.
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.
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.
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.
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.
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?
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |