cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohanV
Helper III
Helper III

Switch Between Hierarchies based on Slicer selection

Hello All,

 

I havea table of organization of employees and their roles as below table.(Data is morphed)

MohanV_0-1615377268643.png

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

MohanV_2-1615377748212.png

 

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.

MohanV_3-1615377766957.png

Any suggestions how can it be done.

 

Thanks,

Mohan V.

 

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

Hi @MohanV ,

 

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"

V-lianl-msft_0-1615527829593.png

 

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 @MohanV ,

 

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!