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     🙂

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.