Forum Discussion

cnschulz's avatar
cnschulz
Helper IV
6 years ago
Solved

Select range of data based on selected

Gday, 

 

I have a set of activities that are performed by users. Esentially, I have USER_ID, ACTIVITY_ID, DATETIME. What Id like to do is when a report user clicks on an activity record in a filter, show all activities that were recorded within a set timeframe of the selected activity. For example (+- 1 hour) from the selected record time. Does anyone know how to go about creating such a thing?

 

Cheers

 

Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    cnschulz 

     

    You have to write a measure to filter another visual.  

    Derive another table. Don't make any relationship with this table.

     

    ActivityTable = SUMMARIZECOLUMNS('Table'[ACTIVITY_ID])

     

    Use this table in the slicer

    Create a measure for filtering the measure.

     

    FilterMeasure =
VAR _activityID =
    CALCULATE (
        SELECTEDVALUE ( ActivityTable[ACTIVITY_ID] )
    )
VAR _selectedDatetime =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATETIME] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ACTIVITY_ID] = _activityID
        )
    )
VAR _minTime =
    _selectedDatetime
        - TIME ( 1, 0, 0 )
VAR _maxTime =
    _selectedDatetime
        + TIME ( 1, 0, 0 )
VAR _datetimeinrow =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATETIME] )
    )
VAR _result =
    IF (
        _datetimeinrow >= _minTime
            && _datetimeinrow <= _maxTime,
        "Show",
        "Hide"
    )
RETURN
    _result

     

    Now you can apply visual level filter 



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

9 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    cnschulz 

     

    You have to write a measure to filter another visual.  

    Derive another table. Don't make any relationship with this table.

     

    ActivityTable = SUMMARIZECOLUMNS('Table'[ACTIVITY_ID])

     

    Use this table in the slicer

    Create a measure for filtering the measure.

     

    FilterMeasure =
VAR _activityID =
    CALCULATE (
        SELECTEDVALUE ( ActivityTable[ACTIVITY_ID] )
    )
VAR _selectedDatetime =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATETIME] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ACTIVITY_ID] = _activityID
        )
    )
VAR _minTime =
    _selectedDatetime
        - TIME ( 1, 0, 0 )
VAR _maxTime =
    _selectedDatetime
        + TIME ( 1, 0, 0 )
VAR _datetimeinrow =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATETIME] )
    )
VAR _result =
    IF (
        _datetimeinrow >= _minTime
            && _datetimeinrow <= _maxTime,
        "Show",
        "Hide"
    )
RETURN
    _result

     

    Now you can apply visual level filter 



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

      • cnschulz's avatar
        cnschulz
        Helper IV
        6 years ago

        I did find an error with this across date boundaries. DATE and TIME loop if you exceed the unit value! So If I use - TIME(48, 0, 0) it will keep the day the same! It also wont work say, if you subtract 1 hour from 01/01/01 00:00:45 itw ill leave the day the same!!!

         

        So to fix this I used simple integer subtraction:

         

        VAR _mydatetime = _olddatetime - 1/24

         

        This will subratct an hour and prederve the date boundaries. 

         

        Thanks everyone for your help.

         

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    cnschulz , something like this

    measure =
    var _maxX = maxx(allselected(Table), Table[Datetime])
    var _max = _maxX + time(1,0,0)
    var _min = _maxX - time(1,0,0)
    return
    CALCULATE(Count(Table[value]), filter(All(Table), Table[Datetime]>=_min && Table[Datetime]<=_max))

    • cnschulz's avatar
      cnschulz
      Helper IV
      6 years ago

      amitchandak cheers, this is close. 

       

      This returns the *count* of all matches however i need to identify the matching rows, either by magically filtering or by setting a binary measure: 1=match 0=no match

       

      we have this:

       

      i need something like this:

       

      I will try the (more complicated) suggestion below and see ho wtha goes.