Aggregate Values from Table A into Table B

[agustinsuarez]

Assume I have Table A with following structure:

Date	Channel	Device	Sessions
5/1/2016	Organic	Desktop	10
5/1/2016	Organic	Mobile	5
5/1/2016	Organic	Tablet	5
5/2/2016	Organic	Desktop	15
5/2/2016	Organic	Mobile	10
5/2/2016	Organic	Tablet	5

I also have Table B with following structure:

Date	Channel	Impressions
5/1/2016	Organic	100
5/2/2016	Organic	200

My objective is to aggregate all the sessions from Table A into Table B, with the following output:

Date	Channel	Impressions	Sessions
5/1/2016	Organic	100	20
5/1/2016	Organic	200	30

As I am still very new to Power BI, I give a similar SQL expression as I would do it using this language: SUM(Sessions) FROM Table A GROUP BY channel.

Note that in the real data there are multiple different values for channel, and therefore I cannot just do a WHERE clause. Thanks!

[Eric_Zhang]

@agustinsuarez

If table A and table B are in a many to one relationship via the columns date and channel, you can create a new column, say joinkey in each table and create relationship against that new column.

In table A
joinKey = TableA[Date]&","&TableA[Channel]

In table B
JoinKey = TableB[Date]&","&TableB[Channel]

Check more details in the attached pbix.zip

[tringuyenminh92 · Accepted solution]

Hi @agustinsuarez,

it could be, but your model is not ideal cause it's many-many. but there is workaround for your requirement

• Create Dates table: Dates= Calendarauto()
• Create Channels table: Channels = values(TableA[Channel])
• Create 4 relationships with 3 actives as picture 
• SS = sum(TableA[Session])

Details of relationships:

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[ImkeF]

This can very easily be done in the query editor: In TableB you merge with TabeA on date and chanel (leave default join-type LeftOuter). Then when you expand the newly created column, you switch to "Aggregate" an choose "Sum" of Sessions.

[Seth_C_Bauer]

@agustinsuarez A date table linked to both of the example tables would allow you to just use your default columns without the need to create a calculation. Something simplistically that look like this.

[Baskar]

Cool dude.

1. Have to create one Table for Master Table . Using Dax Code like the below image 

2. Have to create Relationship between Date Master to Other your Two Tables (Table A , Table B) with Date key like the below image 

  a) Date Master "Date" to Table A "Date"

  b) Date Master "Date" to Table B "Date"

3. Drag Date from Date Master, then Channel, Impresion , session at and all, like below

Let me know if any help

[Baskar]

Can u please tell me what is the relationship between these two tables .

like date to date or Channel to channel ? like this