Forum Discussion

cnschulz's avatar
cnschulz
Icon for Helper IV rankHelper 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     🙂

cnschulz's avatar
cnschulz
Icon for Helper IV rankHelper 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

 

nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity Champion
6 years ago

cnschulz 

 

If the user not selecting any filter then you want to see all data, then you can handle it in the return statement. 

If your scenario is different, then, please submit as a new query along with few valid data and expected output in a tabular form.