Display multiple columns based on slicer selection

[Anonymous]

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:

Customer

AUR

ABC BOT DA

ABC H DA

ABC T DA

FB H DA

FB B DA

FB T DA

ABC H MO

ABC B MO

ABC T MO

FB H MO

FB B MAO

FB T MO

XYZ

DA

123

52

69

ABC

MO

225

2144

125

Desired output:

Customer		AUR	ABC Bot	ABC Human	ABC Tango	FB Human	FB Bot	FB Tango
XYZ		DA	123	52	69
ABC		MO	225	2144	125

Thanks in advance

[Anonymous · Accepted solution]

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