Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I havea table of organization of employees and their roles as below table.(Data is morphed)
Power Query:-
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Dca5DcAgEEXBXn5MEwab0xz5avtvgxeMNGZ6FJTwoaBhYGLLgymyFxkVHT8Wjtwv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CEO = _t, Director = _t, #"Project Manager" = _t, #"Delivery Manager" = _t, #"Tech Lead" = _t, #"Team Lead" = _t, #"Sr Software Engineer" = _t, #"Software Engineer" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CEO", type text}, {"Director", type text}, {"Project Manager", type text}, {"Delivery Manager", type text}, {"Tech Lead", type text}, {"Team Lead", type text}, {"Sr Software Engineer", type text}, {"Software Engineer", type text}})
in
#"Changed Type"
I have created two hierarchies as Seniors and Juniors as below
I have a slicer with values as Seniors and Juniors,
and whenever i select Seniors, i should have Seniors Hierarchy, when i select Juniors from slicer, i should have Juniors Hierarchy.
Any suggestions how can it be done.
Thanks,
Mohan V.
Hi @Anonymous ,
Please try "Unpivot" in Query Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Dca5DcAgEEXBXn5MEwab0xz5avtvgxeMNGZ6FJTwoaBhYGLLgymyFxkVHT8Wjtwv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CEO = _t, Director = _t, #"Project Manager" = _t, #"Delivery Manager" = _t, #"Tech Lead" = _t, #"Team Lead" = _t, #"Sr Software Engineer" = _t, #"Software Engineer" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CEO", type text}, {"Director", type text}, {"Project Manager", type text}, {"Delivery Manager", type text}, {"Tech Lead", type text}, {"Team Lead", type text}, {"Sr Software Engineer", type text}, {"Software Engineer", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "People", each if [Attribute] = "CEO" then "Juniors" else if [Attribute] = "Director" then "Juniors" else if [Attribute] = "Delicery Manager" then "Juniors" else if [Attribute] = "Project Manager" then "Juniors" else "Seniors")
in
#"Added Conditional Column"
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@V-lianl-msft thanks for the reply. much appreciate it.
I have already tried this @V-lianl-msft , but the reason behind i didnt used this method is, this is a dim table and is connected to many fact tables with each specific column for their employee roles related data.
now if i do the unpivot i have to redesign the model which is of more than 40 tables data.
Im looking for, is there any way that we can achive it without doing any data transformation, like with DAX.
Is it possible.?
Please help.
Thanks,
Mohan V.
Hi @Anonymous ,
Unfortunately, this cannot be achieved. In powerbi, the hierarchy is not an actual column and cannot be referenced in a DAX expression.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |