Switch between two different date columns in slicer

[BYENER]

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.

[JasonBreedlove]

Did you ever get this figured out?

[Mrtnkbg]

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

[BYENER]

@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.

[V-lianl-msft]

Hi @BYENER ,

Based on your description, I can probably understand what you think.

It should be pointed out that measure cannot be used as slicer content. To switch slicer options, you could use bookmarks and buttons to switch reports.

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@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]

@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. 

[AlB]

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 

[amitchandak]

@BYENER , You need to join both of them with the date table and use userelation to switch it based on slicer selection

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[BYENER]

Hi @amitchandak 

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