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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mitchell92
Helper II
Helper II

Workload Reporting - Modelling

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 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1670912943646.png

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

RicoZhou_3-1670913225563.png

Result is as below.

RicoZhou_1-1670913204518.png

 

RicoZhou_2-1670913214506.png

 

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.

 

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1670912943646.png

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

RicoZhou_3-1670913225563.png

Result is as below.

RicoZhou_1-1670913204518.png

 

RicoZhou_2-1670913214506.png

 

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.

 

 

Hey Rico,

 

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

 

Mitch

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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