Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Get Slicer values, not related table values

Hi,

 

Is it possible to extract the absolute values or range of a time slicer.

 

 

 

e.g. for the slicer above I want to be able to extract the 13:53 to 14:53. If I use SELECTEDVALUE it will only return within the limits of the rows that meet the criteria. So if my earliest time entry is 13:57, it will return 13:57 instead of 13:53.

 

Ultimately I want to calculate the number of minutes within the time period specified for use in a measure. I considered linking the slicer to a new table to solve my issue, however my understand is that table will only be able to update on scheduled refresh and not as often as my other data which is on direct query.

 

Thanks,

Jordan

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Anonymous 
    You can use 
    MAX/Min instead of the selected value to get the start point and end point of the selected range.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

4 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 
    You can use 
    MAX/Min instead of the selected value to get the start point and end point of the selected range.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      If my selected time range for the last hour is 13:53 to 14:53 and I use MIN to get the value. It will only pull the MIN value from the time column. In this case the earliest entry was 13:57 so it returned that time, rather than the 13:53 which is the bottom limit of the slicer.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    Based on your screenshots, I'm assuming you're using a relative time slicer,  the slicer and filter are always based on the time in Coordinated Universal Time (UTC). You can check the current time zone via UTCNOW()

     

     

     

    Measure = UTCNOW()

     

     

     If you want to extract the time range in the slicer, try the following methods:

     

     

    Measure = 
    var _utc = 
        CONVERT(
            UTCNOW(), 
            DATETIME
        )
    var _utc_minute = 
        TIME(
            HOUR(_utc) - 1, MINUTE(_utc), SECOND(_utc)
        )
return 
    _utc_minute
    & " to " &
    TIME(
        HOUR(_utc), MINUTE(_utc), SECOND(_utc)
    )

     

     

    Here is the result, and you can dynamically get the time range by refreshing

     

    Regards,

    Nono Chen

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks for the response.

       

      The code you have provided would give the desired output, except it would not update when the slicer is changed. Therefore if the slicer showed the last 2 hours, it would still only show a time window for 1 hour.