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     🙂

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

 

nandukrishnavs's avatar
nandukrishnavs
Community Champion
6 years ago

cnschulz 

 

Try this 

FilterMeasure = 
VAR _activityID =

        DISTINCT ( ActivityTable[ACTIVITY_ID] )
    
VAR _minTime = MINX(FILTER(ALL('Table'),'Table'[ACTIVITY_ID] IN _activityID),'Table'[DATETIME]) - ( 1 / 24 )
VAR _maxTime =
    MAXX(FILTER(ALL('Table'),'Table'[ACTIVITY_ID] IN _activityID),'Table'[DATETIME]) + ( 1 / 24 )
VAR _datetimeinrow =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATETIME] )
    )
VAR _result =
    IF (
        _datetimeinrow >= _minTime
            && _datetimeinrow <= _maxTime,
        "Show",
        "Hide" 
    )
RETURN
    _result



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

  • cnschulz's avatar
    cnschulz
    Helper IV
    6 years ago

    Sorry, I neglected to follow up on this. It works perfectly 🙂