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
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
Solved! Go to 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.
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.
Hey Rico,
This is pretty much exactly what I was after. Thank you very much.
Mitch
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
95 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |