DAX Sum Values based on 3 different tables criteria

Hesham121 — Sun, 18 Feb 2024 09:34:39 GMT

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]

'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

Re: DAX Sum Values based on 3 different tables criteria

lbendlin — Sun, 18 Feb 2024 13:50:42 GMT

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?

Re: DAX Sum Values based on 3 different tables criteria

Hesham121 — Sun, 18 Feb 2024 13:11:15 GMT

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?

Re: DAX Sum Values based on 3 different tables criteria

lbendlin — Sun, 18 Feb 2024 14:25:43 GMT

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

topic DAX Sum Values based on 3 different tables criteria in DAX Commands and Tips

DAX Sum Values based on 3 different tables criteria

Re: DAX Sum Values based on 3 different tables criteria

Re: DAX Sum Values based on 3 different tables criteria

Re: DAX Sum Values based on 3 different tables criteria