Forum Discussion

BYENER's avatar
BYENER
Icon for Helper V rankHelper V
5 years ago

Switch between two different date columns in slicer

Hi All,

 

I have two date columns in my table and I am using a slicer in my report. So I will use the final date in my slicer when I choose for final date and otherwise.

 

Can someone help me?

 

Thanks in advance.

Need Help

9 Replies

    • BYENER's avatar
      BYENER
      Icon for Helper V rankHelper V
      5 years ago

      Hi amitchandak 

      Do you mean a switch function in a measure? If yes, the slicer is not working with a measure?

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi BYENER 

    Can you explain with an example what you are trying to do exactly?

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    BYENERI understand you wish to have unique values from two date columns which you want to use in your slicer.  You can create a new table, in powerbi desktop go to the New Table option and paste the below code. It will create a another table with unique date field from two date columns. 

     

    SlicerDate =
    FILTER (
        DISTINCT (
            UNION ( VALUES ( Table1[date_col_1] ), VALUES ( Table1[date_col_2] ) )
        ),
        [SlicerDate ] <> BLANK ()
    )
    • BYENER's avatar
      BYENER
      Icon for Helper V rankHelper V
      5 years ago

      negi007 

       

      No, let me explain. Every row has two dates, creation date and final date. I have a report overview where I want to filter on creation date and sometimes on final date. So for that I want to use a chicklet visual. If the user will use the final date in the slicer then he/she has to click first on date type "Final date" and after he/she can use the slicer. 

       

  • BYENER's avatar
    BYENER
    Icon for Helper V rankHelper V
    5 years ago

    negi007 AlB 

     

    I have a date slicer and I have a chiclet visual (selectable: Final Date or Creation Date). When the end user clicks on "Final Date" than the slicer date should be based on Final date.

     

  • Mrtnkbg's avatar
    Mrtnkbg
    Icon for Advocate I rankAdvocate I
    5 years ago

    Hi BYENER,


    I am a bit late to contribute to this thread, but since it still isn't marked as answered and I had the same question until today when I found an answer, I thought I should share a hint on a possible solution in this thread.


    https://community.powerbi.com/t5/Desktop/Slicer-to-change-Date-Columns/td-p/253185 

     

    The answer gives just a hint, but briefly described I resolved it via right-click on the table containing the dates, selecting Edit Query, selecting the Transform tab, and there using the Unpivot Columns tool (see information link below).

     

    This way one can have a slicer filter, with the slicer property "Selection controls >> Single select" set to "On") with the previous column header value, now available in a new column on rows, in your case "Final Date" and "Creation Date". // Edit: I want to stress the fact that one has to remember to also filter affected measures accordingly, in order to avoid incorrect sums.

     

    Heres a link to information regarding unpivoting: https://docs.microsoft.com/en-us/power-query/unpivot-column 

     

    I like the chiclet slicer as well, but sometimes (as in this case) one can even use the regular slicer, setting the direction property to vertical (i.e. "General >> Orientation" set to "Horizontal") and have square clickable "buttons", much like in your example.

     

    Best regards,
    Mrtnkbg