Combining data based on additional keys

Hi rmorris ,

We have created two sample tables “Sample Table 1” and “Sample Table 2” as you explained your requirement and you want a combined table where comments need to be displayed as comma separated data based on Unique Sales ID.

Please refer to the below screenshot for sample data.

Now we need to create a Merged table, so Navigate to Edit Query and click on Merge Queries as New option as shown in the below screenshot.

This will create a Merged Table automatically, now close the Query editor and create a Column in the Merged Table with below DAX expression:

Comments = CONCATENATEX(FILTER('Sample Table 2', 'Sample Table 2'[SalesID] = Merge1[SalesID]), 'Sample Table 2'[Comment], ",")

Please refer to the below screenshot for Comments DAX and Output for the same.

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at [email protected]

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

hi rmorris 

try to add a calculated column in table1 like:

Comment = 
VAR _sales = [SalesID]
RETURN
CONCATENATEX(
    CALCULATETABLE(
        VALUES(Table2[Comment]),
        Table2[SalesID] = _sales
    ),
    Table2[Comment],", "
)

it worked like: