Forum Discussion
Create slicer groups by selecting columns
Hello, I have the following table and i am trying to present the data in a stacked column visualization with grouping selectable in a slicer based on specific columns. The X axis is the week number...
For that to work you need to change your data model. Unpivot the source data and add the categories there.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDNDoIwDMdfxezMgVXn4K4voAdjCAeFmhAUDaDx8e22Ninx8M/68WvXtqrMGS+jycwJsV8N78cVg3ecn01P7wHn9zhMZO3whs3cfTBkn/eWnv23wWmKxqsbsTV1VhnIAShkSWvShlSQgF9rNRW0JZUh49mJ5bnG1hx1KZMCAbWgsQ13Clm7ZcP9YY4VGwTDS43TmIwC8qlMslzBs+KSoCcoNVZwNGbkMD45Civ544gVjEBy9HVz3U5uXCZHc5bLvRzYST+/4GQ3L1vIhYGWrX8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Year", "Week number"}, "Channel", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Category", each if List.Contains({"Stock","Returns"},[Channel]) then "Warehouse" else if List.Contains({"Defective","Sold"},[Channel]) then "Dock" else "Transport"), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Category", type text}, {"Value", Int64.Type}}) in #"Changed Type"see new version attached
Instead of the three groups you need to create three measures. If you want you can also create a disconnected table with these three choices and then tie the measure result to the selected value. Or use the fancy new Field Parameters.
see attached.
Hi lbendlin Thanks for your reply! The new Field Parameters escaped me! But is it possible to show the categories for each grouping in a legend?
For example, when "Warehouse" is selected, the graph should look as follows:
For that to work you need to change your data model. Unpivot the source data and add the categories there.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDNDoIwDMdfxezMgVXn4K4voAdjCAeFmhAUDaDx8e22Ninx8M/68WvXtqrMGS+jycwJsV8N78cVg3ecn01P7wHn9zhMZO3whs3cfTBkn/eWnv23wWmKxqsbsTV1VhnIAShkSWvShlSQgF9rNRW0JZUh49mJ5bnG1hx1KZMCAbWgsQ13Clm7ZcP9YY4VGwTDS43TmIwC8qlMslzBs+KSoCcoNVZwNGbkMD45Civ544gVjEBy9HVz3U5uXCZHc5bLvRzYST+/4GQ3L1vIhYGWrX8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Year", "Week number"}, "Channel", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Category", each if List.Contains({"Stock","Returns"},[Channel]) then "Warehouse" else if List.Contains({"Defective","Sold"},[Channel]) then "Dock" else "Transport"), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Category", type text}, {"Value", Int64.Type}}) in #"Changed Type"see new version attached