Periods of time with start/stop dates in separate tables

[Anonymous]

Hi everyone!

Super new to Dax so I'm pretty sure I'm missing some obvious stuff... 

I have three separate tables with dates/times for two distinct events (A and B).

The table ares all related via the "Id" (but one Id can have more than one event A, and more than one event B).

So first question, how do I manage the relationships between these tables?

Second question, I would like to find a way to maybe merge tables Start A and End A in a way that would give me a row for each single event A.

And that should help me answer my main question - for context, each event B should be "contained" within one event A. I need to highlight any event B that is not in one event A.

Let's say A is me (Id xxx) using my badge to come in and out of work, and B is me (Id xxx) using the work delivery vehicle. If in my data there is an instance of me using the vehicle for a period that starts before I came in to work and/or ends after I left, then something is wrong. My goal here is to highlight such instances (if it's at all possible...)

Thank you so much in advance!!

Table Start A

Id	Start date Event A	Start time Event A
P-200-E01-201	18-Jan-18	10:30
P-200-E01-202	24-Jan-18	11:30
P-200-E01-207	4-Jun-18	16:12
P-200-E02-203	16-Oct-17	9:00
P-200-E02-203	17-Oct-17	10:30
P-200-E02-204	15-Dec-17	10:50
P-200-E02-205	31-Jan-18	9:00
P-200-E02-205	31-Jan-18	10:00
P-200-E03-201	10-Oct-17	12:30
P-200-E03-201	13-Oct-17	9:00
P-200-E03-201	13-Oct-17	9:50
P-200-E03-201	14-Oct-17	10:00
P-200-E03-201	15-Oct-17	15:00
P-200-E03-201	24-Oct-17	10:15
P-200-E04-202	13-Nov-17
P-200-E05-201	20-Apr-18	16:25
P-200-E06-201	8-Dec-17	12:44
P-200-E07-201	28-Nov-17	15:30
P-200-E07-201	28-Nov-17	16:45

Table End A

Id	End date Event A	End time Event A
P-200-E01-202	29-Jan-18	13:00
P-200-E01-207	8-Jun-18
P-200-E02-203	17-Oct-17	10:00
P-200-E02-203	26-Oct-17	15:10
P-200-E02-204	15-Dec-17	10:55
P-200-E02-205	31-Jan-18	9:45
P-200-E02-205	1-Feb-18	17:00
P-200-E03-201	2-Jan-00	14:30
P-200-E03-201	13-Oct-17	8:50
P-200-E03-201	13-Oct-17	9:40
P-200-E03-201	14-Oct-17	9:50
P-200-E03-201	17-Oct-17	14:30
P-200-E05-201	20-Apr-18	17:10
P-200-E06-201	22-Dec-17
P-200-E07-201	28-Nov-17	16:15
P-200-E07-201	27-Dec-17	15:50

Table B (start and end date are assumed to be the same)

Id	Event B date	Event B start time	Event B end time
P-200-E01-202	24-Jan-18	12:25	12:44
P-200-E01-202	24-Jan-18	16:30	17:12
P-200-E01-202	26-Jan-18	14:23	14:51
P-200-E02-203	16-Oct-17	10:11	10:32
P-200-E02-203	24-Oct-17	7:15	7:31
P-200-E02-203	24-Oct-17	11:13	11:29
P-200-E02-203	24-Oct-17	15:00	15:20
P-200-E02-205	31-Jan-18	10:45	11:00
P-200-E02-205	31-Jan-18	14:05	14:50
P-200-E03-201	10-Oct-17	13:20	13:47
P-200-E03-201	10-Oct-17	13:39	13:47
P-200-E03-201	10-Oct-17	18:00	18:32
P-200-E06-201	8-Dec-17	12:47	13:16
P-200-E06-201	8-Dec-17	16:05	16:31
P-200-E07-201	29-Nov-17	17:00	18:05
P-200-E07-201	30-Nov-17	11:55	12:20
P-200-E07-201	30-Nov-17	15:10	15:29

[v-piga-msft]

Hi @Anonymous,

For your first question, please refer to the below steps:

1. You could Append Table Start A and Table B as a new table.

2. Click ID and remove other columns

3. remove duplicate rows in ID column then you will get the distinct ID.

Then you could create the replationship between table A and ID table with (one to Many) in Relationship view.

For your second question, you could merge the Table Start A and Table End A as a new query and expand the table without Id.

For your further requirement, I'm still a little confused, if the above steps could solved your the first two question, please share the desired output for your last requirement.

Best Regards，

Cherry

[Anonymous]

Thanks so much Cherry!

I followed your steps and they don't exactly give me what I need, BUT I learned about appending and merging and that is definitely going to be useful in general so thanks for that!

Here is the result for one Id. Ideally I should find a way to remove rows 2 and 3, which would leave me with one Event A from 10/16 9AM to 10/17 10AM and another event A from 10/17 10:30AM to 26/10 3:10PM.

Once I have all my events A defined, I need to check that all events B for the same Id happen during one event A.

I guess my end requirements are a bit confusing because I'm trying to use PBI to clean my data rather than report on it. I'd like for the end result to point out stuff that doesn't make sense so I can correct that before doing actual reporting.

I wouldn't be surprised if I was told it's just not possible but I thought I'd give it a try 

Thanks again!!