Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @agustinsuarez,
it could be, but your model is not ideal cause it's many-many. but there is workaround for your requirement
Details of relationships:
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
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
Hi @agustinsuarez,
it could be, but your model is not ideal cause it's many-many. but there is workaround for your requirement
Details of relationships:
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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.
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
Can u please tell me what is the relationship between these two tables .
like date to date or Channel to channel ? like this
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |