Forum Discussion
Linking multiple tables for one visual
I'm trying to find the best way to do this but my limited understanding and knowledge of PowerBI is just frying my brain.
I have 2 main tables:
Table 1 - Timesheet data: Employee entries with each category and hours for each of the categories for every day.
Table 2 - Report 1: These are work orders for customer request.
With a third table to translate the data between table 1 & 2
Table 3 - Remedy customer list: Is like a translate/match table where I've taken the WBS Element from timesheet data and matched it against a customer name in Report 1 and matched it against an order type in report 1.
Then a date table which is linking both table 1 & 2
Please let me know if you need anything else. I really appreciate any and all help / suggestions.
Step 0: I use your data below. (Date: yyyy/mm/dd)
Step 1: I add a 'WBS Element' column to the 'Remedy customer list' table.
WBS Element = [Cat match]&" - "&[Order type]
Step 2: I make a 'Calendar' table.
Step 3: I make a 'WBS-List' table.
WBS-List = SUMMARIZE('Timesheet data','Timesheet data'[WBS Element])
Step 4: I add some relationships below.
Step 5: I make some tables and some matrixs.
9 Replies
I think the cause are some "many-to-many" relationships.
The amount of data doesn't need to be large, so if you can provide me with some concrete 'demo' data I can suggest solutions to improve the situation.Hi Mickey,
Thank you very much for taking the time to look into this for me. Here's some dummy data:
Table 1: Timesheet data Employee Date Qty of hours WBS Element Joe Blogs 01/04/2024 4.5 Internal Meetings Joe Blogs 01/04/2023 2 External customer 1 - Provision Joe Blogs 01/04/2023 1 External customer 1 - Cease Cat Smith 01/04/2024 7.5 Internal Meetings Roger Ralph 01/04/2024 7.5 Admin Joe Blogs 02/04/2024 7.5 External Meetings Cat Smith 02/04/2024 4 Admin Cat Smith 02/04/2024 3.5 External customer 2 - Cease Roger Ralph 02/04/2024 7.5 Admin Table 3: Remedy customer list Cat match Order type Remedy customer Interal customer 1 Provision Scotland Interal customer 1 Cease Scotland Internal customer 2 Provision London Internal customer 2 Cease London Table 2: Remedy data WO ID Order type Remedy customer Submit date WO001 Provision Scotland 01-Apr WO002 Cease Scotland 01-Apr WO003 Provision Scotland 01-Apr WO004 Cease London 02-Apr Step 0: I use your data below. (Date: yyyy/mm/dd)
Step 1: I add a 'WBS Element' column to the 'Remedy customer list' table.
WBS Element = [Cat match]&" - "&[Order type]
Step 2: I make a 'Calendar' table.
Step 3: I make a 'WBS-List' table.
WBS-List = SUMMARIZE('Timesheet data','Timesheet data'[WBS Element])
Step 4: I add some relationships below.
Step 5: I make some tables and some matrixs.