Forum Discussion

srkase's avatar
srkase
Icon for Helper IV rankHelper IV
7 years ago
Solved

MONTH FILTRATION IN SLICER

Hi 

 

I have data like this

 

I want to have one single slicer which should contain months from Apr TO Mar.  Based on that i want to filter the records in matrix table..

 

If We are having a separate column for Month and months are below that, slicer works great.. But I have the data like this only..

 

Kindly guide me 

YEARSHORTNAMEDEALERTYPEPRODUCTVARIANTAPRMAYJUNJULAUGSEPOCTNOVDECJANFEBMARTOTAL QTY
20192020AARJUNBS        A 1202000000005
20192020AARJUNBS        B 0010000000001
20192020AARJUNBS        A 0100000000001
20192020AARJUNBS        C 0020000000002
20192020AARJUNBS        D 3022200000009
20192020AARJUNBS        E 1200000000003
20192020AARJUNBS        F 0010000000001
Need Help
  • JosefPrakljacic's avatar
    JosefPrakljacic
    7 years ago

    Hello Skarse,

     

    you need to transform (unpivot) your table in order to achive the thing you want.

    Here you can see how this would work.

     

    BR,

    Josef

     

    If this post was helpful please share some Kudos and mark it as the solution :smileyvery-happy: Thank you very much!

     

4 Replies

  • JosefPrakljacic's avatar
    JosefPrakljacic
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hello Skarse,

     

    you need to transform (unpivot) your table in order to achive the thing you want.

    Here you can see how this would work.

     

    BR,

    Josef

     

    If this post was helpful please share some Kudos and mark it as the solution :smileyvery-happy: Thank you very much!

     

    • srkase's avatar
      srkase
      Icon for Helper IV rankHelper IV
      7 years ago

      i want to have a single slicer which contains all the months... now if create a slicer, i have to add 12 slicers separately for each month?

       

      • mussaenda's avatar
        mussaenda
        Icon for Community Champion rankCommunity Champion
        7 years ago

        No need to add one by one.

         

        try this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDQyMDJQ0lFyBOEgr1A/IO0UrAAFUAkQbQjERkBsgETjxrE6xJnuBDUdptOQoMmkmO6IZDpxJpNiujOa2wmHCimmu0BNN0Yy3YigLcSa7oolVqnndjfyYjUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YEAR = _t, SHORTNAME = _t, DEALER = _t, TYPE = _t, PRODUCT = _t, VARIANT = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t, JAN = _t, FEB = _t, MAR = _t]),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC", "JAN", "FEB", "MAR"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Only Selected Columns",{{"Value", type number}})
in
    #"Changed Type"