Only show distinct date values in slicer

[Anonymous]

Hi all,

I would like to do the following :

I have rows of data by date, with the date column using this format 2019-12-29 21:00:00.0000000

In Power Query, I duplicated the date column and change the new one to get the name of the day (eg. 'Wednesday').

The goal is for the end user to select a day, and get all the aggregated data for that day.

Problem is : when I create a slicer with this new measure, i still get many rows for each day name.

Is there a trick to only get one 'Sunday', one 'Saturday', etc. in the slicer ?

Best regards,

Martin.

[Tahreem24 · Accepted solution]

@Anonymous ,

First create column like below:

WeekDay = WEEKDAY('Date Table'[Date],1)

Then create second column like below:

DayName = SWITCH('Date Table'[Week],1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednessdat",5,"Thursday",6,"Friday",7,"Saturday")

Then you can put DayName in your slicer and check the result. (Attached screenshot for your reference)

[Anonymous]

Hi,

Thank you all for your help.

I used what I thought was the simplest solution : a calculated column for the slicer (FORMAT('date'[Date],"dddd")), and another to order it (WEEKDAY('Date Table'[Date],1)).

Best regards,

Martin.

[v-cazheng-msft · Accepted solution]

Hi, @Anonymous 

You don't need to duplicate the date column and Measure can't be put into Slicer. You can create a Calculated column and put this column in Slicer.

Weekday = FORMAT('date'[Date],"dddd")

The result looks like this:

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tahreem24 · Accepted solution]

@Anonymous ,

First create column like below:

WeekDay = WEEKDAY('Date Table'[Date],1)

Then create second column like below:

DayName = SWITCH('Date Table'[Week],1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednessdat",5,"Thursday",6,"Friday",7,"Saturday")

Then you can put DayName in your slicer and check the result. (Attached screenshot for your reference)

[VijayP]

@Anonymous 

Create a Date Dimension table and Connect to the Fact Table to get unique Values in the Slicer.

Watch this video to know how to create DateTable

https://www.youtube.com/watch?v=C3ckN6LN6xo&list=PLWQB3PEUJKRmLf93pOxFQFIIe16ucwtW0&index=65