Slicer

[Anonymous]

Hello all,

I have a serious problem now. I have a data of

               A     B      C      D      E

January  2     53    34     15     56

Februar  3     63    94     75     86

March    4     33    74     35     16

April      27    43    64     65     96

May       25    30    44     55     46

 SO is itpossible for me to get a slicer of both Months and A,B,C,D,E separately?

for example I want to see the data of February B with slicer 

Slicer

 Jan

 Feb

 Mar

 April and so on

and another slicer linking to months 

A

B

C

D

E

Can anyone please help me in doing this?

Thank you!

[rbriga · Accepted solution]

Create a calendar table, or a year-month table if that's your smallest date granularity.

It's best practice anyway, and it would yield a year-month field that's independent from the fact table.

If you have no data for 3/2022, for example, your calendar table would still have that month.

Create a relationship beytween 'Calendar'[Year-Month] and 'Data Table'[Year-Month].

Use the 'Calendar'[Year-Month] in the slicer.

[rbriga · Accepted solution]

Create a calendar table, or a year-month table if that's your smallest date granularity.

It's best practice anyway, and it would yield a year-month field that's independent from the fact table.

If you have no data for 3/2022, for example, your calendar table would still have that month.

Create a relationship beytween 'Calendar'[Year-Month] and 'Data Table'[Year-Month].

Use the 'Calendar'[Year-Month] in the slicer.

[Anonymous]

Hey,

Thank for the idea. Let me show my table exactly. So that it would be very easy to understand and find a solution luckily 

In the above picture, I want to make the highlited column as the slicer. This is the exact data 

[rbriga]

Unpivoting will get you the desired solution.

Yes, right now, you will be left with just 2022.

As time progresses, more values would show up.

Please give unpivoting a try.

You can then test it with dummy data for other years and see how it works.

[Anonymous · Accepted solution]

Hey thanks for the  solution. That worked fine. I tried with a new dummy Excel sheet as you said and its working exactly. 

I will accept this as a soluiton to my question. Once again Thankyou 🙂

[rbriga]

Your best option is to unpivot the A-E columns in Power Query.

Unpivot columns - Power Query | Microsoft Docs

Do that, and the A-E would be a field you may add to a slicer, just like months.

[Anonymous]

Unpivoting is ok but I have the data with null values which will be filled out in future. So when I unpivot Iam lossing the months which are having null data. Is there any option for doing this please?