cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rcharan Regular Visitor
Regular Visitor

Display multiple columns based on slicer selection

Hi all,

 

I have couple of challenges, I feel that the second one may not be possible in PBI based on my research but any alternatives would be appreciated.

 

1) Trying to slice the column based data. For this I have created a calculated column which did work but the issue is I have 25 columns that need to be filtered based on the slicer selection and I will end up creating 25 calc columns. Here is my calculation : IF(Table[AUR]="DA",Table[ABC_BOT_DA],BLANK())

 

2) I am trying to display slicer selection related fields only but not other fields. For example if the user selects "DA" from the AUR slicer only DA related fields should be displayed in the grid visual and MO related fields should be hidden. 

 

Sample Data:

CustomerAURABC BOT DAABC H DAABC T DAFB H DAFB B DAFB T DAABC H MOABC B MOABC T MOFB H MOFB B MAOFB T MO
XYZDA1235269         
ABCMO      2252144125   

 

Desired output:

 

Customer AURABC BotABC HumanABC TangoFB HumanFB BotFB Tango
XYZ DA1235269   
ABC MO2252144125   

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
ThomasFoster Established Member
Established Member

Re: Display multiple columns based on slicer selection

I would solve this in the query editor. Your starting data is in a wide format which makes it far harder to work with.

 

I have done these transformations in the below M Code. 

In my example the ABC humans and ABC bot are the other way around, as i think there is a typo in one of your original tables.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMUtJRcnEEEoZGxkDS1AhImFkCCQWCOFYnWsnRyRnI9vXHo9DIyBREGpqYgG0xRTUjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, AUR = _t, #"ABC B DA" = _t, #"ABC H DA" = _t, #"ABC T DA" = _t, #"FB H DA" = _t, #"FB B DA" = _t, #"FB T DA" = _t, #"ABC H MO" = _t, #"ABC B MO" = _t, #"ABC T MO" = _t, #"FB H MO" = _t, #"FB B MAO" = _t, #"FB T MO" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "AUR"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Attribute.2", type text}, {"Value", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.2] = [AUR])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
#"Removed Columns"

In order to run this create a blank query then open it in the advanced editor and paste the code in.

To visualize this in power bi create a matrix visual with customer on the rows, attribute on columns and value on the values

 Hopefully this answers your question

1 REPLY 1
ThomasFoster Established Member
Established Member

Re: Display multiple columns based on slicer selection

I would solve this in the query editor. Your starting data is in a wide format which makes it far harder to work with.

 

I have done these transformations in the below M Code. 

In my example the ABC humans and ABC bot are the other way around, as i think there is a typo in one of your original tables.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMUtJRcnEEEoZGxkDS1AhImFkCCQWCOFYnWsnRyRnI9vXHo9DIyBREGpqYgG0xRTUjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, AUR = _t, #"ABC B DA" = _t, #"ABC H DA" = _t, #"ABC T DA" = _t, #"FB H DA" = _t, #"FB B DA" = _t, #"FB T DA" = _t, #"ABC H MO" = _t, #"ABC B MO" = _t, #"ABC T MO" = _t, #"FB H MO" = _t, #"FB B MAO" = _t, #"FB T MO" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "AUR"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Attribute.2", type text}, {"Value", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.2] = [AUR])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
#"Removed Columns"

In order to run this create a blank query then open it in the advanced editor and paste the code in.

To visualize this in power bi create a matrix visual with customer on the rows, attribute on columns and value on the values

 Hopefully this answers your question