How to find all the records between matching_value and events

[userdata]

Hi,

I have a question in regards to calculating the times when there was a matching value such as a and a and i have to find all the records where operation column is event. So between the matching values a and a, there were few times that event occured. I have to match every matching value and the events that were in between and calculate the time difference and how often there were events recorded per matching value based on their time. So id is the only link when operation column is event and when M and M_required is a match, i can find this through id and matching value, but I have to calculate how often and when exactly the date time was in between this matching values. 

Anyone ideas how to tackle that?

here the link to the file

https://github.com/userdata21/file/blob/master/how%20to%20find%20all%20the%20recordsin%20between%20m...

Thanks!

[amitchandak]

@userdata , I checked data, not clear with your output. can share output data and explain it with an example.

[userdata]

@amitchandak 

Yes sure what i mean is that i have M and Mrequired and I have to find all the records that are events  in between M and Mrequired with their matching values. So when i find a matching pair like s and s, i have to find everything that is called events in between what happened of M and Mrequired.

[userdata]

Hi sorry i just reuploaded the file. So the more precise question is how do i lookup the many table(events) for distinct boundaries. So if  i have m and mrequired as a column in my distinct table I want to look up all the  values that fall between the two dates for when operation is event (in my many table). So if m is 09.04.10 9:00 and mrequired is 10.04.10 6:00  i want to lookup all the values from event and get to know all the dates from events table that fall in between these two records. there can be also more than one event , so how can i lookup these many event for the timerange in my distinct table?

https://github.com/userdata21/file/blob/master/how%20to%20find%20all%20the%20recordsin%20between%20m...

[v-xuding-msft]

Hi @userdata ,

Please try this:

count events = 
COUNTROWS(
    FILTER (
        'events',
        'events'[operation] = "event"
            && events[date] <= 'distinct boundaries'[m]
            && events[date] >= 'distinct boundaries'[m_require]
    )
)

[userdata]

@v-xuding-msft  thanks very much for this! I have a question what if i want to drill into these count events and want to be able to see their id and date so I can calculate timediff?

I tried to create a relationship between distinct boundaries and events where matching value is the unique identifier. And then i did try to put date and id from events into the current table, but then im not able to see anything.

Do you have any idea how i can drill into this count events to be able to see the date from events, so i can see m was at 27.8 at 9am lets say and m_required is 2.9 at 6 am and there were 3 count_events , so i can see these 3 count_events have a date as 28.9 at 5am , 29.9 at 6am and 1.9 at 4am (so i can see all the dates of the events that fall between m and m_required?

[v-xuding-msft]

Hi @userdata ,

There are two workarounds to implement it. One is using drill down with the matrix visual. Another one is to set up drill through in Power BI reports . Please see the .gif to have a try.

[userdata]

@v-xuding-msft  thanks so much for this! But the problem is that if I drill into 7, it should show me 7 events but I only get one event. I looked up the numbers but the numbers are not correct for count_ because each time I go and try to count the events it is not the same as count_?