Charting overallocation of resources against time

[liyanc]

Hi, I'm trying to create a line chart of resource overallocation against time like below:

However, I would also like for the user viewing the report to be able to choose which resources are displayed in the chart.

I have 2 data sources.

The first is a table of assignment work against time. Resources can have multiple assignments on the same day.

The second table is of resource capacity over time.

My current idea involves the following:

1. Group data in Assignment table by day/resource to get each resource's work by day.  

2. Merging the queries (tables 1 and 2) using a custom ID column (concat of TimeByDay and ResourceName).

3. Subtracting Capacity from WorkByDay to get Overallocation.

4. Pivoting ResourceName column to give the following table:

5. Adding the individual fields (resource) to the line chart.

My questions are:

1. I would like for the user to be able to select which resources are shown on the chart (e.g. via a slicer). As each resource is currently an individual field, is this possible?
2. Additionally, I have a table of detailed resource information (e.g. resource name, ID, department, etc). Is there a way to directly map the overallocation table to the resource information table (i.e. mapping rows to fields) or transform the resource information table then map fields such that the resources displayed in chart can be filtered by department? 
   
3. Is there a more efficient/better method of achieving this?

Thank you in advance for any advice!

[v-shex-msft · Accepted solution]

HI @liyanc,

#1,  For your scenario, I do not so recommend you to change your table structure, new structure not suitable for your requirement.

In my opinion, I'd like to suggest adding a calculated column to concatenate 'date' and 'resource name' fields to these tables.  Then you can create a calculated table with merged concatenate fields as a bridge to link two tables.

Calculated column:

Resource Date =
Table[TimeByDay] & "-" & Table[ResourceName]

Calculated table:

Bridge =
ADDCOLUMNS (
    DISTINCT (
        UNION ( VALUES ( Table1[Resource Date] ), VALUES ( Table2[Resource Date] ) )
    ),
    "TimeByDay", PATHITEM ( SUBSTITUTE ( [Resource Date], "-", "|" ), 1 ),
    "ResourceName", PATHITEM ( SUBSTITUTE ( [Resource Date], "-", "|" ), 2 )
)

After these steps, you can simply use bridge table fields to analytic records between two tables.

Relationship in Power BI with Multiple Columns 

#2, You can link the detailed information table with the bridge table 'ResourceName' field.

Regards,

Xiaoxin Sheng