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

[Anonymous]

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 is to display  a pie chart by goal types with the corresponding conversion label name. And  dynamically change label names when filtering by account type.

I have tried to use this DAX: (However is not working):

Goal Types = IF(('All Data'[Account]="Random1" && 'All Data'[Data Source]="GA Main Website Accounts" && 'All Data'[Goal 1]>0), "Hero customer form submission",

IF(('All Data'[Account]="Random1" && 'All Data'[Data Source]="GA Main Website Accounts" && 'All Data'[Goal 2]>0), "Product find out more CTA",

IF(('All Data'[Account]="Random2" && 'All Data'[Data Source]="GA Main Website Accounts" && 'All Data'[Goal 1]>0), "Apply now CTA",

IF(('All Data'[Account]="Random2" && 'All Data'[Data Source]="GA Main Website Accounts" && 'All Data'[Goal 2]>0), "Discover more CTA",

IF(('All Data'[Account]="Random3" && 'All Data'[Data Source]="GA Main Website Accounts" && 'All Data'[Goal 1]>0), "Lead generation sign up form",

IF(('All Data'[Account]="Random3" && 'All Data'[Data Source]="GA Main Website Accounts" && 'All Data'[Goal 2]>0), "Find your car CTA","Other"))))

Please somone can help me with this?

I have attached a sample data source here:

https://docs.google.com/spreadsheets/d/1BRcYbk4lSt4gpTIT61aJB2k1x9x-n4DN8L7igcjqkeg/edit?usp=sharing

[Anonymous]

Hi @Anonymous,

AFAIK, current power bi does not support create dynamic calculated column/table based on filter/slicer effect.

If you mean dynamic change the visual titles, you can create a measure and setting the visual title based on expressions results.

Expression-based titles in Power BI Desktop - Power BI | Microsoft Docs

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[Anonymous]

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

[Anonymous]

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]

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]

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]

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.

[Anonymous]

Hi @Anonymous,

It seems like you are work with a table with multiple value fields that you need to dynamic changes.

In my opinion, I will recommend you do 'unpivot columns' on these value fields to convert these attributes and values, then you can simply build visualizations based on these fields. (use matrix instead of table visual)

Dynamic Attributes In A Power BI Report 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi ,

Thank you for your help, 

I am not able to use the 'unpiviot' function  as I am using a table created by unioning different other tables.

Is there any other solutions?

[Anonymous]

Hi @Anonymous ,

Please refer to this video,

https://www.youtube.com/watch?v=yEemVBiaTuk

Thanks

[Anonymous]

@Anonymous  Thank yoiu for sharing this, I have already seen this video and in my case I am using a table union which is including different accounts for which changing naming for Goal 1 or 2 or others may not be beneficial as goal types may have different goal names based on a specific Account.