Custom slicer to select and display multiple columns

[Anonymous]

Hello,

I am trying to create a custom slicer based on multiple columns. Here is the data I have currently: 

aa

bb

cc

dd

2020 A

2020 B

2020 C

2021 A

2021 B

2021 C

2022 A

2022 B

2022 C

a1

b1

c1

d1

Customer 1

a2

b2

c2

d2

Customer 2

a3

b3

c3

d3

Customer 3

I want to create a slicer such that I can select one or multiple specific years. For example, if I select "2020" in the slicer, the output would look like this:

aa	bb	cc	dd		2020 A	2020 B	2020 C
a1	b1	c1	d1	Customer 1
a2	b2	c2	d2	Customer 2
a3	b3	c3	d3	Customer 3

How can I do this?

Really appreciate your help - thanks!

[danextian]

Hi @Anonymous 

You need to unpivot all columns but customer so those column names go to the rows and then split or extract the year and category (ABC) from the unpivoted columns. Your data should something like below after the transformation:

Please see attached pbix for your reference.

[Anonymous]

Thank you for your reply. I actually have more columns in addition to the Customer column that I would like to preserve (see below). Does the aforementioned method also work? I'm worried that it might be too messy.

aa	bb	Customer	dd	ee	2020 A	2020 B	2020 C	2021 A	2021 B...
a1	b1	Customer 1	d1	e1
a2	b2	Customer 2	d2	e2
a3	b3	Customer 3	d3	e3

Really appreciate your help - thank you!

[v-yiruan-msft]

@danextian Thanks for your contribution on this thread.

Hi @Anonymous ,

@danextian has provided the possible solution, here need to make a little adjustment: select the columns which include year info and only unpivot these selected columns in Power Query Editor. Please find the details in the attachment.

1. Only unpivot the columns which include year info

= Table.Unpivot(#"Changed Type", {"2020 A", "2020 B", "2020 C", "2021 A", "2021 B", "2021 C"}, "Attribute", "Value")

2. Create a Text filter and matrix visual with below settings 

Best Regards

[Anonymous]

Hello @v-yiruan-msft ,

Thank you so much for your reply. I am experimenting with the method you just provided.

Due to user demand, the output visual has to be a table, instead of a matrix, which is why I expanded the columns as 2020A, 2020B, 2020C... earlier, instead of unpivoting them into a single Attribute column. 

Could you kindly help with this situation? Is there any way to overcome this roadblock? How could I modify your method to create a table output? Thank you.

[v-yiruan-msft]

Hi @Anonymous ,

If you can't unpivot these columns with the year inside the power query editor then they are multiple columns and not one. I have updated my pbix file(see the attachment), please check if it is what you want.

Best Regards

[Anonymous]

Hi @v-yiruan-msft,

Thank you so much for your response. This is working great!

However, I have encountered 1 problem and 2 follow-up questions.

1. The visual works perfect when I select a single year in the slicer, but it would not work when I multi-select. It could not display ALL years as well. For my data, I have 4 years that I would like to either display 1, multiple, or all years. Could you kindly help on this topic?
2. Currently, when I select 2020 in the Year slicer, columns 2021 A, B, and C would be blank but still displayed. Is it possible to hide and not display the columns that weren't selected in the Year slicer?
3. The table visual you created has columns aa, bb, cc... selected from "Table 2", and 2020A, 2020B, 2020C... selected from the "Year" table. Because I plan to refresh the Power BI dashboard weekly with new data pasted to the same excel file, is it possible to select 2020A, 2020B, 2020C... from "Table 2" instead of "Year"? How can I modify the current code for the slicer to still work?

Thank you. Really appreciate your help!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I updated my pbix file(see the attachment), please find the details in it.

The below one is my answer of your above questions:

1. It has been achieved in my updated pbix file.

2 and 3: I'm sorry those two things can't be accomplished. Regarding your third question, you can add the latest year to the YEAR table to avoid that the latest year's data can't be rendered in table visual. 

Using unpivot+matrix is the best solution to solve your three problems. (Years are multi-selected, unselected years are not shown in the visual, slicer options are from this table)

Best Regards

[Anonymous]

Hello @v-yiruan-msft,

Yes, this is looking great! Thank you so much for all the responses. Really appreciate it!