Issue with creating a table visual from related tables.

[jellyjoe]

Hello,

I have an issue with creating a relationship between three tables.

First let me introduce you to the data model.

We have 3 tables:

(Please note Primary keys are used for something else, they won't help with creating this relation)

1. Conv_Stops

PrimaryKey	Timestamp	Line	Conv	Duration	Type
1111	2021-06-24 09:00:44:24	AM1	CONV-1	140	STOP
2222	2021-06-24 09:33:44:24	AM1	CONV-1	178	STOP

2. MC_Stops

PrimaryKey	Timestamp	Line	MC	Duration
3333	2021-06-24 09:00:22:24	AM1	MC-1	70
4444	2021-06-24 09:21:22:24	AM1	MC-1	110
5555	2021-06-24 12:21:22:24	AM1	MC-1	10

3. Conv_MC_Relations

Line	Conveyor	Pitch	Machine
AM1	CONV-1	1	MC1
AM1	CONV-2	2	MC2
AM1	CONV-3	3	MC3
AM1	CONV-4	4	MC4
AM1	CONV-5	5	MC5

I've created a relation like this:

Where the relations are

MC STOPS -----(connected by MC = Machine)---- CONV_MC_RELATIONS------(connected by conveyor = conv)------ CONV STOPS

My goal is to create a table(visualisation) like this:
(Shows all the Conv_Stops)

Conveyor	Timestamp(from Conv_stops)	Duration(from Conv_stops)	caused by machine
CONV-1	2021-06-24 09:00:44:24	140	MC1
CONV-1	2021-06-24 09:33:44:24	178

Where caused by machine will write the machine if:

[MC timestamp] starts before [Conv timestamp] AND
[MC timestamp + MC Duration] ends after [Conv timestamp + Conv Duration]
OR
[MC timestamp + MC Duration] starts after [Conv timestamp] AND
[MC timestamp] starts before [Conv timestamp]

Is there a way i can achieve this?
Thanks in advance for the help.

Meddled with the file a bit, here's link to current PBIX.

Link to PBIX: https://easyupload.io/43lnuc

[jellyjoe]

Thank you four your support!
That is what I'm looking for except that I'd like to see ALL conveyor stops and just have a caused by MC written in a column if it happened to be caused by one. If not then that column would be left blank.
Is that possible?

[Kumail]

@jellyjoe 

Please excuse, however, this is not a very common practise as both the tables 'dbo.MC_Stops' and 'dbo.Conv_Stops' have many to many relationship and not a single record in 1 table relate to a single record in other table.

Although it is possible, however, it would require new virtual table to be created to apply this thing.

Regards

Kumail Raza

Did this help? Kudos are appreciated

Consider Accepting it as the solution to help the other members find it more quickly.

[jellyjoe]

I see, still thank you very much for your help.
I'll mark this issue as resolved and make another post about the virtual table.

[Kumail]

Hello @jellyjoe 

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards
Kumail Raza

[jellyjoe]

Sure thing!

I've uploaded it here: https://easyupload.io/43lnuc

In the meantime i've tried messing with it a little, managed to create a table (seen on the report) that catches when the times overlap, but i'm not sure how to achieve the effect i wrote about.

Regards

Damian Gęglawy

[Kumail]

@jellyjoe 

This is giving access denied error.

[jellyjoe]

The link? 
Might be because of organizational restrictions. Unfortunately i cannot share it from onedrive or dropbox outside my organization due to my own restrictions.

[Kumail]

@jellyjoe May be you can share that from google drive.

[jellyjoe]

https://drive.google.com/file/d/1SZ5twz9ucJkOXqb0u927-m139a__-kaJ/view?usp=sharing

Here's the google drive link, hopefully that works.

[Kumail]

Hello @jellyjoe 

There are a few questions;

-What is the key to relate records between tables 'dbo.Conv_Stops' and 'dbo.MC_Stops'? If we take "Line+ConveyorConnection" then it is common between all the records in both tables.

-Is this logic correct for ConvStopCausedByMachine?

Regards

Kumail Raza

[jellyjoe]

Hello @Kumail 

The key is Line+Conveyor connector and Machine which are matched to each other using the Conv_MC_Relations table.

The logic is that we know that machine X and conveyor Y have a relation, then we get times of stops from conveyors and time of stops of machines and compare them looking for certain events.

The measure i wrote is correct, those are the events that I'm looking to see 1 being the event happened, 0 being the event didn't happen.

My main issue is i don't know how to display that data in a form of a table showing something looking like that:

line + Conveyor connector	Conv stop start	Conv stop end	Caused by Machine
AM1 CONV12	2021-06-24 09:00:44	2021-06-24 09:03:04	MC1 (or blank if no match)

The way of determining ..caused by machine" would be the measure 1 being caused by machine and 0 not being caused by it.

Sorry if my explanation is a bit chaotic, if You have any more questions please let me know.

[Kumail · Accepted solution]

Hello @jellyjoe 

Sample .pbix file is attached for your reference.

https://drive.google.com/file/d/121sNOM_K4cHRRP6bWrJUJSwKJvS2OA-8/view?usp=sharing

Regards

Kumail Raza

Did this help? Kudos are appreciated

Consider Accepting it as the solution to help the other members find it more quickly