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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @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"

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.

Anonymous
Not applicable

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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