Forum Discussion
Best practice: Aggregating Merging columns from two tables
I have two tables with one field in common. I'm trying to basically create a new table that has just a few columns from both tables, using one of the columns (employee ID) as a reference. What I would like to end up with is something like a matrix table that sums/counts values across the columns for each employee ID. Can you suggest article or best way to create a new table?
so, Anonymous it's still should aggregate the data correct in power BI desktop version
also, for the cases like that is a good practice to create a bridge calculated table
bridgeTableEmployee = DISTINCT('Table A'[EmployeeID])then create 2 pairs of relationships (many to one both):
Table A -> bridge
Table B - > bridge
do not hesitate to give a kudo to useful posts and mark solutions as solution
az38... ah, I see what I did wrong in the formula... this looks much better..! I can use the bridge in some other reports that have the same issue of many to many.. thanks very much and have a good new year holiday!
8 Replies
Anonymous
usually in the most common and simpliest case, you don't need a new table, just create relationships between tables and aggregate your data in visual
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks, that's even better not creating a new table... question on the relationship... Table A has the data the end users are wanting to use. Table B has additional columns they would like to see. Both tables have EmployeeID to join with. Table A should look up EmployeeID from Table B (similar to vlookup). Is there a particular type of join i need to use( inner, outer, left, right) so Table A uses only the EmployeeID needed from Table B?
Anonymous
the best scenario is one-to-many relationships
if each EmployeeID in Table A is unique (one row=one EmployeeID), you create a visual then you dont even need to vlookup something, just add fields from different tables in your visual
do not hesitate to give a kudo to useful posts and mark solutions as solution