If slicer is selected, then show specific column

[ElisaJhang08]

Hi,

I’m the new to power BI, and will appreciate if anyone could help me to solve this simple problem.

Scenario:

There are 4 mobile brands here(1,2,3,4)

All of them sell 4 kinds of mobile(iiphone, ssony, oopo and samsongg).

In the process of manufacturing, each mobile takes 4 IC type (IC-A, IC-B, IC-C, IC-D) as material. Take mobile barand"1" "iiphone" for example, it contains 100 IC-A from red, 70 IC-B from yellow...

5 IC suppliers(yellow, red, black, white, orange, blue) provide IC-A to IC-D

NOTE: each IC supplier may not provide all kinds of IC, take ”black” for example, it doesn’t provide IC-C

I want to build up a dashboard that after selecting IC Brand and IC Type, user can understand sum of IC usage from that brand, take “black” & “IC-A” for example, i only want to see the red circle column, while others may mislead user, or I select “black” & ”IC-A” & ”IC-B”, or I select “black” &”White”& “IC-A”, the table only show columns that I select.  The logic may like:

Show IC-A Usage=IF(Slicer=IC-A, sum of IC-A, “”)

Show IC-B Usage=IF(Slicer=IC-B, sum of IC-B, “”)

Show IC-C Usage=IF(Slicer=IC-C, sum of IC-C, “”)

Show IC-D Usage=IF(Slicer=IC-D, sum of IC-D, “”)

I have no idea about dealing with this kind of problem, and hope the senior of power BI can give me a hand about this, thanks!

[v-angzheng-msft · Accepted solution]

Hi, @ElisaJhang08 

Your table structure dictates that more filtering fields are needed to get the results you want.
You can create a multi-field slicer and then select only the corresponding fields for example in the image below.

Of course this solution is not what we expect, so I found a second solution, unpivot some columns in the table and then add a custom column to determine if the values are displayed or not, then we can get the result as follows:

Now we have the desired result.

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZYxb8IwEIX/S2YqxQmBdqyTpVMHRsQALQLUlCDaCvHva5PgvLMbBvy6RHZ0/hKfz+/efJ6oZJTsdodts1+b0XH9bp4qTc3zvK7r5mQGUzFTYzs9bXffdoHKCvN8KR+eu9WLEY+p+xgmtvwfbOVeWmpmZrOvZn/GldkFu6p/7MJHGE9grPK0Tyn8J4Gor+9IvJLMq5CXi3PpQp9sZHNc7jdu1p5Y3g9VAQkk4nQfywGWcGfiaRXQxmb82hwaXN4Grurl24er8BYxluWdQvpgvxSmduE0JGaRAqzkvbMnM1t+msrebABUCBFQYpbiLEtFOV73TsPqBHWHhi3/BzusktfQQhS9ylAopPJOofewmd5l50AH9fJ+ZJjPXkV6kGxnCuQDdRh109t7PNRr5hwo3n0OUbQgi+zkRLZ7N5MSgrKMt14eUCxR04meI2IgsRtJhyWavmNMsLol3hPQ7mxY0Jsm836slE8WtQp/FlQZDUH3jTyUZPQWfxj3WJ7wmAxgmMlY4g2b6bkB8AmoE563CL06B6qTIX94L1E0IA6ySgKXiJXe+oQMmMgX1kzadvSIkUDMIwHnG85IXBVsF+pb9nusdu+8hIMI2g6L6meSwfSaDwsrjZGsdVfFRX82mecTLg3PcxGhN6Jxh+96BNRLLY2Ld37xCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Mobile Brand" = _t, Product = _t, #"IC-A Brand" = _t, #"IC-A Usage" = _t, #"IC-B Brand" = _t, #"IC-B Usage" = _t, #"IC-C Brand" = _t, #"IC-C Usage" = _t, #"IC-D Brand" = _t, #"IC-D Usage" = _t, #"IC Type" = _t, #"IC Brand" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mobile Brand", Int64.Type}, {"Product", type text}, {"IC-A Brand", type text}, {"IC-A Usage", Int64.Type}, {"IC-B Brand", type text}, {"IC-B Usage", Int64.Type}, {"IC-C Brand", type text}, {"IC-C Usage", Int64.Type}, {"IC-D Brand", type text}, {"IC-D Usage", Int64.Type}, {"IC Type", type text}, {"IC Brand", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Mobile Brand", "Product", "IC-A Brand", "IC-B Brand", "IC-C Brand", "IC-D Brand", "IC Type", "IC Brand"}, "Attribute", "Value"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Unpivoted Other Columns", {"Mobile Brand", "Product", "IC Type", "IC Brand", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "IC-X Usage"}, {"Value", "Usage value"}, {"Attribute.1", "IC-X Brand"}, {"Value.1", "X-Brand"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.StartsWith([#"IC-X Usage"],[IC Type])and Text.StartsWith([#"IC-X Brand"],[IC Type])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true))
in
    #"Filtered Rows"

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft]

Hi, @ElisaJhang08 

You can do something with Pivot, Unpivot, ISFILTER function, etc.
Not sure about your model structure. Please share your sample pbix file's link here so that i may work out with a workaround.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ElisaJhang08]

Hi @v-angzheng-msft ,
here are pbix and raw data's share link

https://drive.google.com/file/d/133JaQyKuYvfvDOW3Jlz0HGTqnhD-slkc/view?usp=drivesdk

https://docs.google.com/spreadsheets/d/1nYkPZ-R1Y9agxXiXRO-zerKk_NXOqpst/edit?usp=sharing&ouid=11424...

Looking forward to your good news, and thanks for your assustance!

[ElisaJhang08]

Additional explanation for the problem:

or let other columns' number display "0" is also ok !