DAX Sum Values based on 3 different tables criteria

[Hesham121]

Hello Dears,

I've the below data set and I want to create a measure that calculate total number of trips with the below critera:

- Sum 'DR_DUTIES'[# Of Trips]

If

'ROSTER DATABASE'[DATE] = 'APP_TT'[Date]

&

'APP_TT'[Applied TT] = 'DR_DUTIES'[TimeTable]

& 

'DR_DUTIES'[Driving Duty] = 'ROSTER DATABASE'[Activity]

I need to SUM the # Of Trips and total Driving Hours per Employee based on Date, Activity, and Applied Timetable

1- DR_DUTIES Table:

Driving Duty	TimeTable	# Of Trips	Driving Hours
M-01	Ph3BTTR_TH_GIZA	6	6:52:57 AM
M-02	Ph3BTTR_TH_GIZA	5	5:47:14 AM
E-01	Ph3BTTR_TH_GIZA	5	5:30:41 AM
E-02	Ph3BTTR_TH_GIZA	6	6:47:20 AM
M-01	Ph3A_WE_LUXOR	5	4:18:32 AM
M-02	Ph3A_WE_LUXOR	6	5:13:27 AM
E-01	Ph3A_WE_LUXOR	6	5:13:27 AM
E-02	Ph3A_WE_LUXOR	6	5:13:27 AM
M-01	Ph3BTTR_WD_ASWAN	5	5:29:32 AM
M-02	Ph3BTTR_WD_ASWAN	6	5:04:59 AM
E-01	Ph3BTTR_WD_ASWAN	6	6:34:00 AM
E-02	Ph3BTTR_WD_ASWAN	6	5:05:30 AM

2- Applied TT Table:

Date	Applied TT
01-Jun-23	Ph3BTTR_TH_GIZA
02-Jun-23	Ph3BTTR_TH_GIZA
03-Jun-23	Ph3BTTR_WD_ASWAN
04-Jun-23	Ph3A_WE_LUXOR
05-Jun-23	Ph3BTTR_TH_GIZA
06-Jun-23	Ph3A_WE_LUXOR
07-Jun-23	Ph3BTTR_WD_ASWAN
08-Jun-23	Ph3BTTR_WD_ASWAN

3- ROSTER DATABASE Table:

CODE	Date	Activity
Emp01	01-Jun-23	E-02
Emp01	02-Jun-23	E-01
Emp01	03-Jun-23	M-02
Emp01	04-Jun-23	M-01
Emp01	05-Jun-23	E-02
Emp01	06-Jun-23	E-01
Emp01	07-Jun-23	M-02
Emp01	08-Jun-23	M-01

Expected Results should be

45 Trips and 43:32 Driving hours during the period 1-Jun-23 to 8-Jun-23 for the Emp01

[lbendlin]

That seems to be straightforward, you should be able to use data model and TREATAS or USERELATIONSHIP to handle that.  What have you tried and where are you stuck?

[Hesham121]

I've tried to connect the data model, but it wasn't able to get the correct results and I don't why... maybe there are a problem with the data modeling, Can you suggest the best connection between tables please?

[lbendlin · Accepted solution]

Here is one possible implementation.  Note that my result differs from yours.