Forum Discussion
Aggregate Values from Table A into Table B
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!
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
6 Replies
Can u please tell me what is the relationship between these two tables .
like date to date or Channel to channel ? like this
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
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.
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- 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.
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
