Linking multiple tables for one visual

[Anonymous]

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.

[mickey64]

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.

[Anonymous]

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

[mickey64 · Accepted solution]

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.

[Anonymous]

Hi Mickey, thank you for doing that. I've managed to replicate what you've got on my table. The only difference is, the one-to-one relationship is a one-to-many WBS-list (one) to Remedy customer list (many).

Now how can I take this and make a visualisation bar chart with the WBS Element in the X Axis and then the Sum of quantity (hours from timesheet data) and the count of WO as another bar, both in the Y Axis? The Sum of quantity is showing correctly in the Y axis but the count of WO is just counting all WOs in every X axis category.

[mickey64]

How about this graph?

This problem of 'the count of WO' could be solved if there was a key column in 'Remedy data' table to set a relationship with 'WBS-List' table, but I am unable to create a key column because I do not understand the meaning of each piece of data.

[Anonymous]

Thank you so much for sticking with me here. So this is what I'm trying to achieve but when adding the Count of WO ID, it is giving the same number for each WBS element category, but it should be showing:

External Customer 1 - provision: 2

External Customer 1 - Cease: 1

External Customer 2 - Cease: 1

[mickey64]

You can solve the problem if you can add a "WBS Element" column to the "Remedy data" table and add a relationship.
I don't know the details of your data, so I can't solve the problem, but I'm sure the data you have has the information to add a "WBS Element" column.

[Anonymous]

Ahh ok I've created a new column in the Remedy data table and used the LOOKUPVALUE DAX formula and that's worked. Thank you so much for your help with this!!

[Anonymous]

Somehow I forgot to put my ask in the main post.. 

How can I create a visual which will show the total hours logged against each customer and the total new orders placed for each customer, both on the Y Axis and the WBS Element text on the X Axis.