Forum Discussion
DAX Sum Values based on 3 different tables criteria
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
Here is one possible implementation. Note that my result differs from yours.
3 Replies
- lbendlin
Super User
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?
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
Here is one possible implementation. Note that my result differs from yours.
