Custom Grouping Calculations of Source/Medium from Google Analytics

[lemarcfj]

Hi everyone,

This is my first time posting to this forumn so please excuse any repitition or if I'm not following the rules. 

I have a table that pulls in the source/medium from Google Analtyics. I'd like to use this dimension to create a custom calc/column in PowerBI that is based off of the rules I provide. When I was using Tableau at my previous job, this was an easy calculated field using the following syntax: 

If Contains([source/medium], 'Facebook') OR  Contains([source/medium], 'Instagram')

THEN "Social"

ELSEIF Contains([source/medium], "cpc") 

Then "Paid Search" 

END

..etc. This calc would create a new header, column, and dimension based off my rules and I could use it like any other dimension. I'm hoping that someone could help me create a similar calculated measure for PowerBI. I hate that I have to relearn calculation syntax, specifically DAX, but it is what it is. Has anyone else had to releard syntax from Tableau to BI? Any help/resources for this calculation and for making the transition would be greatly appreciated. 

Thanks!

[v-xjiin-msft · Accepted solution]

Hi @lemarcfj,

Try following expression with a calculated column:

Test =
IF (
    OR (
        FIND ( "Facebook", [source/medium] ) > 0,
        FIND ( "Instagram", [source/medium] ) > 0
    ),
    "Social",
    IF ( FIND ( "cpc", [source/medium] ) > 0, "Paid Search" )
)

If it doesn't satisfy your requirement, please share us some sample data and its corresponding expected result. So that we can make some proper tests.

Thanks,
Xi Jin.

[lemarcfj]

Thanks for your reply everyone. I actually ended up adding a Conditional Column where I was able to build IF/THEN logic using the build in function. Much appreciated for those who lent a hand. 

[lemarcfj]

Also, it looks like I can't use the Group feature because there are just too many source/medium combinations to manually select them all. I will need to be able to use "IF Contains" type of logic. 

[ChandeepChhabra]

@lemarcfj : Instead of writing an IF Statement (which can be long), you can write the Switch Statement like this 

=SWITCH('Table'[Source / Medium],"Insta","Social","FB","Social","YouT","Social","Orga","Organic","Dire","Organic","Paid","In-Organic")