Forum Discussion

Heinrich's avatar
Heinrich
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Slicer for Weekdays and Hours

Hello

Hope you are doing well.

I would like to do 2 slicers.

1. Weekday: Monday to Sunday

2. Hours

1. I copied the Date Column and managed to have only the Name of the Weekdays but when I drag this 2nd Column to a slicer than it is not showing the single weekdays

2. I copied the Time Column and filtered only the hour, than I don't see the hours.

Do you have an Idea
Heinrich

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Shahid12523's avatar
    Shahid12523
    1 year ago

    Don’t copy columns — create proper dimension tables:

     

    Weekdays (Mon–Sun):

    DimWeekday =
    DATATABLE("Weekday", STRING,
    {{"Monday"},{"Tuesday"},{"Wednesday"},{"Thursday"},{"Friday"},{"Saturday"},{"Sunday"}})


    Hours (0–23):

     

    DimHour =
    ADDCOLUMNS(GENERATESERIES(0,23,1),"HourText",FORMAT(TIME([Value],0,0),"hh:00"))


    Relate them to your fact table and use as slicers. This way slicers show unique weekdays & hours.

  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    Heinrich 

     

    Create a proper calculated column for weekday:

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

    To sort weekdays in correct order (Mon–Sun), add another column

    Weekday No = WEEKDAY('Table'[Date],2) 

    In the model, select Weekday column → “Sort by Column” → choose Weekday No

     

    If your time column is DateTime, create:

    Hour = HOUR('Table'[Time])
    Hour Label = FORMAT('Table'[Time], "HH:00")
    💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
    Best regards,
    Kedar
    🌐 Connect on LinkedIn

3 Replies

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Don’t copy columns — create proper dimension tables:

     

    Weekdays (Mon–Sun):

    DimWeekday =
    DATATABLE("Weekday", STRING,
    {{"Monday"},{"Tuesday"},{"Wednesday"},{"Thursday"},{"Friday"},{"Saturday"},{"Sunday"}})


    Hours (0–23):

     

    DimHour =
    ADDCOLUMNS(GENERATESERIES(0,23,1),"HourText",FORMAT(TIME([Value],0,0),"hh:00"))


    Relate them to your fact table and use as slicers. This way slicers show unique weekdays & hours.

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    Heinrich 

     

    Create a proper calculated column for weekday:

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

    To sort weekdays in correct order (Mon–Sun), add another column

    Weekday No = WEEKDAY('Table'[Date],2) 

    In the model, select Weekday column → “Sort by Column” → choose Weekday No

     

    If your time column is DateTime, create:

    Hour = HOUR('Table'[Time])
    Hour Label = FORMAT('Table'[Time], "HH:00")
    💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
    Best regards,
    Kedar
    🌐 Connect on LinkedIn