cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cnschulz
Helper IV
Helper IV

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, show all activities that were recorded within a set timeframe of the selected activity. For example (+- 1 hour) from the selected record time. Does anyone know how to go about creating such a thing?

 

Cheers

 

1 ACCEPTED SOLUTION
nandukrishnavs
Community Champion
Community Champion

@cnschulz 

 

You have to write a measure to filter another visual.  

Table.JPG

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

 

Capture.JPG

Now you can apply visual level filter 

Capture.JPG



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

View solution in original post

9 REPLIES 9
nandukrishnavs
Community Champion
Community Champion

@cnschulz 

 

You have to write a measure to filter another visual.  

Table.JPG

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

 

Capture.JPG

Now you can apply visual level filter 

Capture.JPG



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

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

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.

 

@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

 

@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

Capture.JPG



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

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

 

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

amitchandak
Super User
Super User

@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))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@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:

 

Annotation 2020-05-15 121322-2.png

I will try the (more complicated) suggestion below and see ho wtha goes.

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors