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...
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     🙂

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

    nandukrishnavs Thank you very much for the detailed solution. It works perfectly. 🙂

    • 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.

       

      • cnschulz's avatar
        cnschulz
        Helper IV
        6 years ago

        nandukrishnavs Im sorry to bother you again, Ive been asked for an extension to this but just cant get it to work 😞

         

        We would like the ability to select *multiple* actions on in one list and then show *all* actions that fall within the time buffer of any of the selected actions. Ie: many-to-many instead of one-to-many. Therefore the selected item would have to be a table, not an individual id. 

         

        Doe anyone have a similar exampe they can demo?

         

        Thanks again