Best practice: Aggregating Merging columns from two tables

[Anonymous]

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?

[az38 · Accepted solution]

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]

@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

[Anonymous]

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?

[az38]

@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

[Anonymous]

That's the part I'm trying to figure out... the EmployeeID's in Table A and Table B is not unique. For example, an employee may have several different audits during a day. When I created a relationship between the two tables, the only type it would allow is many to many.

[az38 · Accepted solution]

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

[Anonymous]

Hi @az38  I'm doing something wrong or missed a step...? I created the bridge table and linked the NetworkID's in both tables... the relationship is showing many to many, and doesn't allow selecting one to many...

[az38]

@Anonymous 

it looks strange, because I see a star symbol "*" near bridge table.

It could means the only one - your values NetworkID in bridge are not unique

are sure you create this table like

= DISTINCT('Table A'[NetworkID])

?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous · Accepted 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!