Forum Discussion

tuulia's avatar
tuulia
Helper IV
1 year ago
Solved

Time in slicer

Hi!

 

I have a time-column in Time-dimension (hours and minutes). In SQL Server, Tabular Model and PowerBI visual the time format shows in correct format 08.00, 08.01, 08.02 etc.

 

Like in this visual, the time-format is correct:

 

 

But in slicer things get weird..

In slicer same hour/minute-column is like this. Between-selection does not work at all.

 

 

What can I do? Between selection is mandatory.

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for following up. If displaying the time with a dot or colon (like 08:30 or 08.30) inside the slicer is a must, here's the best workaround given Power BI's current limitations:

    You can still use the TimeInMinutes (or a decimal field) for the slicer logic, but pair it with a display column that shows the time in the format you need. Here's how you can do it:

    TimeDecimal = HOUR([YourTimeColumn]) + MINUTE([YourTimeColumn]) / 60
TimeLabel = FORMAT([YourTimeColumn], "HH:mm")

     If your locale forces a comma in TimeDecimal, and you just need the label to show a dot or colon, create a custom label column:

    TimeLabelDot = SUBSTITUTE(TimeLabel, ":", ".")

    Then use a custom slicer visual (like Smart Filter by OKViz or HierarchySlicer) that lets you show labels but filter using the underlying value.

    Power BI's built-in slicer doesn’t support formatted display with separate logic, so using a custom visual is the most flexible option here.

     

    Best Regards,

    Hammad.

9 Replies

  • BhavinVyas3003's avatar
    BhavinVyas3003
    Super User
    1 year ago
    1. In your Time Dimension table, create a new calculated column and Use this TimeDecimal field in the Between slicer.

    TimeDecimal = HOUR([YourTimeColumn]) + MINUTE([YourTimeColumn]) / 60

    1. Display your X-axis in visuals using the original formatted Time column.
    2. You can optionally format the TimeDecimal to look like time using a calculated display column:

    TimeLabel = FORMAT([YourTimeColumn], "HH:mm")

    • tuulia's avatar
      tuulia
      Helper IV
      1 year ago

      Thank you. Slicer still behaves weird

       

      I tried to use decimal number, but locale-settings turns dot to comma. Changing locale/language in PBI service or browser is not an option. I can only do changes in PowerBI Desktop or in Tabular Model.

       

       

      Is it possible to force dot to decimal numbers (instead of comma)?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi tuulia,

    Thanks for reaching out to the Microsoft fabric community forum.

    You're right, this issue is caused by how Power BI handles time-only fields in slicers. When a column contains only time (without a date), Power BI assigns a default base date (30.12.1899), which is why you're seeing that in the slicer. Unfortunately, the "Between" slicer doesn't work well with time-only values.

    The workaround by BhavinVyas3003, suggests using a decimal column (like TimeDecimal = HOUR(...) + MINUTE(...) / 60) is technically correct, but as you noticed, the decimal separator (dot vs comma) depends on your Power BI Desktop locale. Since you're not able to change the locale settings in Power BI Service or the browser, here's a practical solution:

    Try creating a new column that represents time in minutes (as a whole number). This avoids decimal separators entirely and works smoothly in a "Between" slicer.

    TimeInMinutes = HOUR([YourTimeColumn]) * 60 + MINUTE([YourTimeColumn])

    Then, use this column in your slicer. For visuals, you can still use your original formatted time column (FORMAT([YourTimeColumn], "HH:mm")) for display.

     

    I would also take a moment to thank BhavinVyas3003, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

    • tuulia's avatar
      tuulia
      Helper IV
      1 year ago

      Thank you! Unfortunately, a dot (or a colon) must be displayed in the timepart in slicer..

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for following up. If displaying the time with a dot or colon (like 08:30 or 08.30) inside the slicer is a must, here's the best workaround given Power BI's current limitations:

        You can still use the TimeInMinutes (or a decimal field) for the slicer logic, but pair it with a display column that shows the time in the format you need. Here's how you can do it:

        TimeDecimal = HOUR([YourTimeColumn]) + MINUTE([YourTimeColumn]) / 60
TimeLabel = FORMAT([YourTimeColumn], "HH:mm")

         If your locale forces a comma in TimeDecimal, and you just need the label to show a dot or colon, create a custom label column:

        TimeLabelDot = SUBSTITUTE(TimeLabel, ":", ".")

        Then use a custom slicer visual (like Smart Filter by OKViz or HierarchySlicer) that lets you show labels but filter using the underlying value.

        Power BI's built-in slicer doesn’t support formatted display with separate logic, so using a custom visual is the most flexible option here.

         

        Best Regards,

        Hammad.