Forum Discussion
cnschulz
Helper IV
6 years agoSelect 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...
- 6 years ago
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 _resultNow you can apply visual level filter
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
nandukrishnavs
Community Champion
6 years ago
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
Helper IV
6 years agonandukrishnavs Thank you very much for the detailed solution. It works perfectly. 🙂