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.
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?
Solved! Go to 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... 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!
@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
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.
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
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...
@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
@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!
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |