Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TaylorLowrey
Frequent Visitor

Unable to create multiple relationships to one field

I have a table of data that has multiple employee name columns. The other data columns apply to each employee column individually for tracking purposes.

Sample table looks something like..
Emp1. Emp2. Emp3. Items. Minutes

I would like to be able to summarize the data several ways such as..
minutes per item per employee
Average minutes per item
Total items per employee
Etc..

The problem that i am experiencing is that because Emp A could be in any of the 3 columns, i cannot seem to find a way to summarize by name across the 3 Emp fields.
6 REPLIES 6
TheOckieMofo
Resolver II
Resolver II

Having 3 employee columns doesn't seem to make sense to me here. Sounds to me like you need to un-pivot the data via power query then the measures would be very simple. Unless you really need to delineate whether the employee in question was coded as emp1, emp2, or emp3. 

The reason for the employee columns is merely how the data is collected at the source. Essentially multiple employees work together on a line, data is recorded at one instance and assigns the employees that are currently on the line into employee name columns.

I guess ill do some reading on un pivoting data

@TaylorLowrey it is fine you are getting the dta the way it is but unpivot will work, post sample data here and I will get you the solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Start DateEnd DateSubmission NumberExport App NameUsernameUser Last NameUser First NameResponse IDResponse Date/TimeDevice Date/TimeSubmission Form NameSubmission Form VersionShiftEmp 1Emp 2Emp 3RoomProduct Type# of LotsLot NumberProduct CodeProduct DescriptionLot Number2Product Code (#2)Product Description (#2)TimeIn1TimeOut1TimeIn2TimeOut2TimeIn3TimeOut3TimeIn4TimeOut4Items WeighedItems in Lot #1Items in Lot #2Total TimeItems In BlendMin/Item
4/15/2019 12:00:00 AM4/17/2019 11:59:59 PM00001WeighingxxEmployeeProduction4529bc7c-800a-4525-a09e-5238fbda77e604/15/2019 20:20:5704/15/2019 20:19:44Weighing21EMP 85EMP 82Weigh Room 1TabletDouble Lot           8551881023.5
4/15/2019 12:00:00 AM4/17/2019 11:59:59 PM00002WeighingxxEmployeeProduction1f03669b-8315-469d-a33e-179f476f54b704/15/2019 23:16:5204/15/2019 23:15:38Weighing22EMP 99EMP 84Weigh Room 1CapsuleSingle Lot            31 331 

I unpivoted the data, that is working great for giving me the data that i need by Employee. I think i need to input another table with the same data as a secondary source now though. After unpivoting the data, my other numbers were ballooned due to creating a new row for each EMP column.

parry2k
Super User
Super User

@TaylorLowrey you need to unpivot your data then it all will be very easy. You can unpivot data very easily in power query.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.