Summarized Table Between Two Datasets

[PowerBIUser9901]

Hi, I need guidance in how I can create a summarized table between two datasets. Dataset 1 includes Sale #s and Item Count, while Dataset 2 contains Transactions #s and Item Count. There can be more than one transaction for each Sales#. The key in these datasets are the Date & Category.

The Screenshots below show the datasets and the desired output: (The colors show how the data is related between datasets)

Desired output:

I tried to build a relationship using the Key (Date&Category) and create a Summairzed Table, however I could not get it to work. 

I included the PBIX file if that helps. Link Below:

https://onedrive.live.com/?authkey=%21AO0A56O38ZamOgM&id=5E807BAB3F3348D%21108&cid=05E807BAB3F3348D

I appreciate any help you can provide. Thank you!

[v-angzheng-msft · Accepted solution]

Hi, @PowerBIUser9901 

I have tried to modify your formula, according to the conditions you provided,

if you filter the data by the key you created, you will get the following result

Result:

Calculate table =
SUMMARIZECOLUMNS (
    'Key'[Key],
    "Transaction Number",
        CONCATENATEX (
            VALUES ( 'Dataset 2'[Transaction#] ),
            'Dataset 2'[Transaction#],
            ","
        )
)

The data on July 2 cannot be distinguished by the key alone,

if you want to separate it you need more conditions.

Is this the result you want? Hope this is useful to you.

Please feel free to let me know If you have further questions.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.