Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Appending Calculate table with normal table.

I have this calculated table in power bi

My_Trackable_Links_Table =
CALCULATETABLE( SUMMARIZECOLUMNS( 'Adobe_Trackable_Links'[Date],
// 'Adobe_Trackable_Links'[Pfizer Customer ID],
'NTID_Table'[EmailAddress],
'NTID_Table'[Pfizer Customer ID],
'Trackable Link Mapping'[Activity]
),
'Trackable Link Mapping'[Exists] = "Exists"  // Assuming "Exists" is of type Text
)

Also I have this table called "New_Attribution_Model" and contain the following columns (Source, EmailAddress, and Date) and also other columns

I want to create a new calculated table from the two tables above as merged table and only merge the common columns which are ( Source, EmailAddress, and Date)

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI Anonymous,

    You can try to use the following calculate table formula to create a new table that pickup and union two table specific fields:

    Merged =
UNION (
    SELECTCOLUMNS (
        'New_Attribution_Model',
        "Source", [Source],
        "EmailAddress", [EmailAddress],
        "Date", [Date]
    ),
    SELECTCOLUMNS (
        'My_Trackable_Links_Table',
        "Source", [Old Source],
        "EmailAddress", [EmailAddress],
        "Date", [Date]
    )
)

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous,

    How did these two table mappings? What key field are you used to link two table records? Please provide more detail information then we can try to merge these table field based on relationship mapping.
    How to Get Your Question Answered Quickly  

    In addition, please also told us the field name that stored the value you wanted to output, then we can use SELECTCOLUMNS function to choose and rename them to Source, EmailAddress, and Date you wanted.

    SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn

    Regards,
    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 
      Thank you so much for your response.

      The main table, named "New_Attribution_Model," has 71 columns, with the key ones being [EmailAddress, Date, and Source]. The "Source" column indicates the corresponding activity for each email. Note that an email can be associated with multiple activities, and we'll later determine the primary activity using the Earliest Date function.

      The second table is a calculated one named "My_Trackable_Links_Table" and has 5 columns: [EmailAddress, Activity, Source, Date, and Customer ID]. The "Activity" and "Source" columns in this table have the old names of activities, and "Source" is the updated name.

      Now, the goal is to append the second table beneath the first one, considering the common columns [EmailAddress, Date, and Source]. Other columns should be ignored, or if absent in the second table, treated as blank.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        HI Anonymous,

        You can try to use the following calculate table formula to create a new table that pickup and union two table specific fields:

        Merged =
UNION (
    SELECTCOLUMNS (
        'New_Attribution_Model',
        "Source", [Source],
        "EmailAddress", [EmailAddress],
        "Date", [Date]
    ),
    SELECTCOLUMNS (
        'My_Trackable_Links_Table',
        "Source", [Old Source],
        "EmailAddress", [EmailAddress],
        "Date", [Date]
    )
)

        Regards,

        Xiaoxin Sheng