Workload Reporting - Modelling

[Mitchell92]

Hi all,

I'm trying to figure our the best way to model this. Our organisation has a table where, per "Job" (key in below example), when "Events" are registered, they're registered by date in one column, and employee in a subsequent column.

Example below:

KEY	Event1Date	Event1RegUser	Event2Date	Event2RegUser	Event3Date	Event3RegUser
1	15/11/2021	EMPLOYEE1	20/11/2021	EMPLOYEE2	25/11/2021	EMPLOYEE3
2	16/11/2021	EMPLOYEE2	18/11/2021	EMPLOYEE1	19/11/2021	EMPLOYEE1
3	17/11/2021	EMPLOYEE2	19/11/2021	EMPLOYEE2	25/11/2021	EMPLOYEE3

I want to be able to measure workload per employee and be able to drill down to what employees have actioned what "Jobs" (or key) over a timeframe.

I can do this via QTY and date quite easily by using a "USERELATIONSHIP" measure after I have created a seperate employee table and date table. The measure is like so - calculate(count(event1), userelationship(employeetable, event1reguser), userelationship(datetale, event1date), which gives me something like this (can filter on datetable to change quantities over time):

Employee	Event1	Event2	Event3
EMPLOYEE1	1	1	1
EMPLOYEE2	2	2	0
EMPLOYEE3	0	0	2

What I cannot do is tie back the "Job" / key to these employees.

Just looking for help if anyone has run into this problem before / has any solutions. Ultimately I'd like to visually display a list of employees, how many jobs they've actioned over a timeframe and what events they have registered in one table. Unfortunately I think the modelling of the original table hinders this quite a bit, so I may have to look at creating an SQL view to model this more effectively.

Thank you,

Mitch

Example 

[v-rzhou-msft · Accepted solution]

Hi @Mitchell92 ,

Here I suggest you to transform your table in Power Query.

You can copy and paste the code as below in Advanced Editor in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI01Dc01TcyMAJxXH0DfPwjXV2hEkYG6BJGUAkMHcZKsTrRSlBpQzMc+gwtcNhkaIkpATLQGCptjstADH34XBgLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KEY = _t, Event1Date = _t, Event1RegUser = _t, Event2Date = _t, Event2RegUser = _t, Event3Date = _t, Event3RegUser = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"KEY", Int64.Type}, {"Event1Date", type date}, {"Event1RegUser", type text}, {"Event2Date", type date}, {"Event2RegUser", type text}, {"Event3Date", type date}, {"Event3RegUser", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"KEY", "Event1RegUser", "Event2RegUser", "Event3RegUser"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"KEY", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1","Date","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","RegUser","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = [Attribute.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Date"}, {"Value.1", "RegUser"}, {"Attribute", "Event"}})
in
    #"Renamed Columns"

New Table:

Then it will be easier to connect it with dimtables like Date table, Key table and so on.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mitchell92]

Hey Rico,

This is pretty much exactly what I was after. Thank you very much.

Mitch