Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to dynamically change column field names ? (For: Google Analytics goal types)

Hello,   I am reporting some Google Analytics data, and I am struggling to dynamically change gol types (e.g: 1 and 2) into their corresponding conversion names at account level.   My objective i...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you for your help. But is not what I am looking for. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous,
So you mean to group your records based on fields value and assign specific categories instead of change field names? If that is the case, you can try to use the following DAX formulas:

Goal Types =
IF (
    'All Data'[Data Source] = "GA Main Website Accounts",
    SWITCH (
        'All Data'[Account],
        "Random1",
            IF (
                'All Data'[Goal 1] > 0,
                "Hero customer form submission",
                IF ( 'All Data'[Goal 2] > 0, "Product find out more CTA", "Other" )
            ),
        "Random2",
            IF (
                'All Data'[Goal 1] > 0,
                "Apply now CTA",
                IF ( 'All Data'[Goal 2] > 0, "Discover more CTA", "Other" )
            ),
        "Random3",
            IF (
                'All Data'[Goal 1] > 0,
                "Lead generation sign up form",
                IF ( 'All Data'[Goal 2] > 0, "Find your car CTA", "Other" )
            )
    )
)

Regards,
Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for your help,

     

    What I am trying to do is to amend the column goal name in the correct goal name. 

     

    For instance on Account Random1 Goal 1 in its native Google analytic account is called "Hero customer form Submission". Hence, I would like to assign the correct naming by type of goal number based on a specific Account. 


    Hope this make sense

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      HI Anonymous,

      In fact, I already add the 'switch' function to handle account values with different conditions.

      If the fact table includes a large number of account values, I'd like to suggest you create a mapping table with account value and two types of sceniaro fields.
      After these steps, you can simply search values from the mapping table and changes the current calculated column values.

      Goal Types =
IF (
    'All Data'[Goal 1] > 0,
    LOOKUPVALUE (
        MappingTable[Scenario 1],
        MappingTable[Account], 'All Data'[Account]
    ),
    IF (
        'All Data'[Goal 2] > 0,
        LOOKUPVALUE (
            MappingTable[Scenario 2],
            MappingTable[Account], 'All Data'[Account]
        ),
        "Other"
    )
)

      Regards,

      Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thank you for this, I have attached a  screen shoot of the issue that I am facing.

         

        I have created a column called goal measure names that is grouping all goals columns in one field .

        Goal Measure names =
        IF('All Data'[Goal 1], "Goal 1",
        IF('All Data'[Goal 2], "Goal 2",
        IF('All Data'[Goal 3],"Goal 3",
        IF('All Data'[Goal 4],"Goal 4",
        IF('All Data'[Goal 5], "Goal 5",
        IF('All Data'[Goal 6], "Goal 6",
        IF('All Data'[Goal 7], "Goal 7",
        IF('All Data'[Goal 8], "Goal 8",
        IF('All Data'[Goal 9], "Goal 9",
        IF('All Data'[Goal 10], "Goal 10",
        IF('All Data'[Goal 11], "Goal 11",
        IF('All Data'[Goal 12], "Goal 12",
        IF('All Data'[Goal 13], "Goal 13",
        IF('All Data'[Goal 14], "Goal 14",
        IF('All Data'[Goal 15], "Goal 15",
        IF('All Data'[Goal 16],"Goal 16",
        IF('All Data'[Goal 17], "Goal 17",
        IF('All Data'[Goal 18], "Goal 18",
        IF('All Data'[Goal 19], "Goal 19",
        IF('All Data'[Goal 20], "Goal 20",BLANK()))))))))))))))))))))

        Then I have created another column goal types that is the same I have mentioned above.


        With the visual I have attached I am trying to allocate the specific goal number to the specific goal name in a table. So people may understand what goal 1, goal 2 and other goals are.

         

        However, it seems that for goal conversion values of goal 15 and 7  are attributed to more than one goal type names. Foe instance for goal 15 it should appears 120 conversions only for the goal type Email CT and blank for the other ones. Same case for goal 7. While goal 19 is not showing up in both goal measure names and goal type names as its value is registered within the goal 3 measure name category.

         

        Also I am filtering data based on account  and campaign types.

         

         

        I am not sure how  can I  handle this.

         

         Please let me know if you can help me with this.