Return a Row if multiple conditions match another row

[Anonymous]

Hello

I am pretty sure this has been covered before however since i am very new to PBI i think i need a little more hand-holding. I have a load of xyz data of a number of objects. I have appended the tables together and i want to return the rows where the:

• dt is equal to another AND
• obj_x(device_id_A) is within 1m of obj_x(device_id_B) AND
• obj_y(device_id_A) is within 1m of obj_y(device_id_B) AND
• device_id is different

I would also like it to work for multiple device_ids (even though i only have 2 in this example). 

I would really appreciate some help. 

Many thanks in advance. 

Will

dt	x	y	device id
3/4/2020 17:00:11	4	10	A
3/4/2020 17:00:11	3	20	B
3/4/2020 17:00:12	10	30	A
3/4/2020 17:00:12	11	29	B
3/4/2020 17:00:13	4	23	A
3/4/2020 17:00:13	5	24	B
3/4/2020 17:00:11	4	10	A
3/4/2020 17:00:11	3	20	A

[parry2k]

@Anonymous what is expected output?

[Anonymous]

Sorry, expected output is the row or rows which meet this criteria or a 'true' statement in a new column. My end result will be a heatmap of the objects which occupy the same space at the same time. 

[v-kelly-msft]

Hi @Anonymous ，

Can you show me a direct output presentation?Such as a table visual of the output,it's a bit confused about the criteria...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly

Here is a table of the expected output, i either need a filter to only print the yellow lines or an extra column to create a filter of the yellow lines in the visual. I am trying to identify one or more different devices which occupies the same 1m radius at the same point in time. 

I have attached the image of the complete data set which has the location of the devices at 1 second intervals throught the day however i want to filter this on when different devices are together (within a 1m radius) at the same time. A 1m square may be easier to begin with. 

Many thanks

Will

Please note, this table is slightly different to the first, i have made the last 2 entries a different time for simplicity. Please ignore the first. 

[parry2k]

@Anonymous I would love to solve it, it is too late for me, if you can put sample data in excel with expected calculation, I will work on it tomorrow. I hope someone able to get you the solution before I work on it.

[Anonymous]

Many thanks @parry2k, here is a sample of the dataset in csv format, this should make it easier. 

https://1drv.ms/u/s!AhxwTE9UMcGrg1BoBpiB0OIhv6CT?e=YHPHpH

Really appreciate  your help and looking forward to learning more about PBI

Will 

[parry2k]

@Anonymous I have to catch some sleep, file is great but can you take a sample data out of it and perform the calculation with the result showing binary (1 or 0 ) filter or not filter, just add another tab. That will speed up to write measures in PBI. 

[Anonymous]

@parry2k, not sure if i understood you. Did you want me to filter the sample file or were you asking if binary output is okay. Binary output is perfect since i can use this to slice the visual. If you want me to pre-process the data then please give me more info, i don't understand. 

Do you think this will require a sophisticated solution in PBI? I am wondering if i am better off diving back into Python? 

Thanks

Will

[parry2k]

@Anonymous you request is not clear, so i simply asked, that data you provided in csv format, just take sample data out of it and create your logic in excel and share how you would like to filter the rows and then we can take that logic and deploy in power bi, it is not about it can be done or not, it is more about understanding what to do. Keep in mind, more detailed and info your provide, quickly community will able to help you.

[Anonymous]

@parry2k I have almost recreated the solution in excel however i am not quite there, i will try and explain. I have 3 filters on the table, the first is to check if the x value of the row in question is within the range of any of the other rows (within 2m), the second is the y value and the third is to check if the time stamps are the same. When i filter on all three i get problems because the second filter filters on the results of the entire dataset and not on the results of filter 1. I want filter 2 to work on filter 1 and filter 3 to work on filter 2. 

You can see from the image that some of the points are clustered within 2m of each other however the timestamps on these 2 do not match, hence no use. I am looking for pairs or clusters of data points within 2m and with the same timestamp. 

here is a Venn Diagram i am trying to create for each cluster, not the entire dataset. 

I hope this makes sense but i have clearly reached the limit of my excel skills as well as my PBI (that was a long time ago). 

position_data.xlsx

Many thanks

Will

[Anonymous]

Hi @parry2k @v-kelly-msft 

Have you had a chance to think about this? I hope my explaination is clear. As i said in my post above, i have managed to create the filters in excel but each filter operates on the entire dataset instead of the results of the previous filter. 

I have succeeded to perform the filter using python but i don't want to run that script every time i want to adjust the filters. 

Any thoughts on some tips on how to move forward would be really appreciated. 

Thanks

Will