Forum Discussion
Custom slicer to select and display multiple columns
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!
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
8 Replies
- danextian
Super User
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.
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!
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
