Filter data within (calculated) data range

[p1ngu1n]

Hi guys,

I am about to get mad, either I do not see the solution or does not exist 😉

I have data from an Azure SQL server which I do access via direct query and like to keep it as much as possible.

In one table (A) I have a number of records which are structured as element, start date/time, end date/time. In the other table (B) I have events with timestamps only.

I would like to filter the events (B) with timestamps whenever I click, chose one of the records in (A). So basically I need a logic to filter the table (B) with the timestamp between start and and dates from the record in (A).

Is that even possible? I only see a solution in creating a table thats filtered, but that does not look to be possible in a dynamic way.

Any ideas?

Thanks in advance!

[v-lid-msft · Accepted solution]

Hi @p1ngu1n ,

We can create a measure and use it in visual filter to meet your requirement:

Measure = IF(SELECTEDVALUE(Event_B[Timestamp]) >= MIN('Table_A'[start])  && SELECTEDVALUE('Event_B'[Timestamp]) <= MAX('Table_A'[end]),1,-1)

Best regards,

[p1ngu1n]

@v-lid-msft Unfortunately I found a problem. This does not work for the first and the last item in the table.

 MIN('Table_A'[start])

MAX('Table_A'[end]),1,-1) 

do not return any value for the first and last item in the table.

Any idea why?

[v-lid-msft]

Hi @p1ngu1n ,

If you are meaning there are two items such as following in Table A,

record	start	end
First Record	2019-1-1 00:00:00	(Blank)
...	...	...
...	...	...
Last Record	(Blank)	2021-12-31 23:59:59

we can try to use the following measures:

Measure =
IF (
    MIN ( 'Table_A'[start] ) = BLANK (),
    IF ( MAX ( 'Table_A'[end] ) >= SELECTEDVALUE ( Event_B[Timestamp] ), 1, -1 ),
    IF (
        MAX ( 'Table_A'[end] ) = BLANK (),
        IF ( MIN ( 'Table_A'[start] ) <= SELECTEDVALUE ( Event_B[Timestamp] ), 1, -1 ),
        IF (
            SELECTEDVALUE ( 'Event_B'[Timestamp] ) >= MIN ( 'Table_A'[start] )
                && SELECTEDVALUE ( 'Event_B'[Timestamp] ) <= MAX ( 'Table_A'[end] ),
            1,
            -1
        )
    )
)

Best regards,

[p1ngu1n]

@v-lid-msft Awesome! That is a really nice and quick solution, working smoothly! Many thanks!