Forum Discussion
Helper IIssue with creating a table visual from related tables.
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
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
12 Replies
jellyjoeSure 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
- 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
Impactful Individual
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.