Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I have two tables.
First table: Table with this columns about some calls: date and hour [dd:mm:YY hh:mm:ss], travel code, client, call duration [hh:mm:ss]
Second table: Table with some columns about the travels: travel code, start date travel [dd:mm:YY hh:mm:ss], end date travel [dd:mm:YY hh:mm:ss], start hours [hh:mm:ss], end hours [hh:mm:ss]
So I made a relationship between then, using travel code. Now I need to know the percent of call made during the travel using dax measure. Look this line for example:
First table: travel code: 250015; call started at 04/13 08:22:00; call ended 04/13 08:22:34
Second table: travel code: 250015; start travel 04/13 08:00; end travel 04/13 13:30
This travel should be counted. After count all of this cases, we should divide by the total of calls.
Hi, @IgorAM
If you just want to get the percent of calls in the corresponding time period, please formulas like:
Count1 = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Call started]>=SUM(Table2[Start travel])&&Table1[Call ended]<=SUM(Table1[Call ended])))
All count = COUNT(Table1[Travel code])
Result = [Count1]/[All count]
Best Regards,
Community Support Team _ Eason
Thank you for the answer,
I think it's almost this, I tried this formulas, but it does not work. I'll give you another example:
Call table:
Call_Date_Hour | Travel Code | Client | Call duration |
14/04/2020 16:24 | 18791668 | Cliente 1 | 00:00:36 |
19/04/2020 07:13 | 18805913 | Cliente 1 | 00:00:59 |
19/04/2020 21:48 | 18811065 | Cliente 1 | 00:00:36 |
19/04/2020 22:23 | 18811065 | Cliente 1 | 00:00:33 |
19/04/2020 22:25 | 18811065 | Cliente 1 | 00:00:31 |
23/04/2020 10:30 | 18824537 | Cliente 1 | 00:00:50 |
23/04/2020 14:06 | 18827367 | Cliente 1 | 00:00:30 |
23/04/2020 14:23 | 18827651 | Cliente 1 | 00:01:02 |
23/04/2020 16:52 | 18823981 | Cliente 1 | 00:00:43 |
24/04/2020 11:35 | 18828915 | Cliente 1 | 00:00:58 |
24/04/2020 11:41 | 18828915 | Cliente 1 | 00:00:44 |
24/04/2020 12:10 | 18828915 | Cliente 1 | 00:00:32 |
24/04/2020 12:36 | 18827367 | Cliente 1 | 00:00:32 |
24/04/2020 19:19 | 18834085 | Cliente 1 | 00:00:32 |
24/04/2020 19:28 | 18834085 | Cliente 1 | 00:00:30 |
25/04/2020 08:32 | 18834940 | Cliente 1 | 00:00:36 |
25/04/2020 08:33 | 18834940 | Cliente 1 | 00:00:33 |
26/04/2020 11:26 | 18834085 | Cliente 1 | 00:00:36 |
08/05/2020 19:26 | 18889171 | Cliente 9898 | 00:00:31 |
08/05/2020 19:30 | 18889171 | Cliente 9898 | 00:00:31 |
09/05/2020 09:35 | 18889171 | Cliente 9898 | 00:00:45 |
09/05/2020 12:06 | 18889171 | Cliente 9898 | 00:00:36 |
Travels table:
Travel code | Travel_Date_start | Travel_Date_end |
18791668 | 2020-04-14 05:49:01.000 | 2020-04-15 18:42:52.000 |
18805913 | 2020-04-16 18:54:38.000 | 2020-04-23 14:23:45.000 |
18811065 | 2020-04-19 10:44:06.000 | 2020-04-20 06:26:44.000 |
18824537 | 2020-04-23 05:18:45.000 | 2020-04-23 11:30:48.000 |
18827367 | 2020-04-23 12:33:25.000 | 2020-04-27 10:50:54.000 |
18827651 | 2020-04-23 13:11:03.000 | 2020-04-23 14:25:51.000 |
18823981 | 2020-04-22 19:44:46.000 | 2020-04-24 10:59:39.000 |
18828915 | 2020-04-23 18:07:51.000 | 2020-04-24 12:10:48.000 |
18827367 | 2020-04-23 12:33:25.000 | 2020-04-27 10:50:54.000 |
18834085 | 2020-04-24 18:05:08.000 | 2020-04-26 18:32:09.000 |
18834940 | 2020-04-24 21:36:06.000 | 2020-04-27 12:17:50.000 |
18889171 | 2020-05-09 09:27:30.000 | 2020-05-12 23:08:40.000 |
We have to compare this two tables, travel by travel. For example:
14/04/2020 16:24 | 18791668 | Cliente 1 | 00:00:36 |
This call hapenned in the travel.
We know that because if we look for this travel code on the travel table, we'll find:
18791668 | 2020-04-14 05:49:01.000 | 2020-04-15 18:42:52.000 |
The call was at 14/04 16:24, after the travel begins (14/04 05:49) and before it ends (15/04 18:42). So I have to count it.
Now, let's take another example:
08/05/2020 19:26 | 18889171 | Cliente 9898 | 00:00:31 |
If we look for this travel code on the travel table, we'll find:
18889171 | 2020-05-09 09:27:30.000 | 2020-05-12 23:08:40.000 |
The call was at 08/05 19:26, before the travel begins. In this case, I don't count this row on call table. If this call were after the travel ends, we don't need to call it too.
So I have to count the cases like the first example (travel 18791668) and divide by all the call we had. Then, I'll find a percentage.
The second example (travel 18889171) was just to show what we should not count.
Hi, @IgorAM
Not fully sure what you want. Can you share you expected result for further investigation?
You can try formulas as below:
diff1(seconds) = DATEDIFF(SUM(Table1[Call started]),SUM(Table1[Call ended]),SECOND)
diff2(seconds) = DATEDIFF(SUM(Table2[Start travel]),SUM(Table2[End travel]),SECOND)
Percentage = [diff1(seconds)]/[diff2(seconds)]
If I have misunderstood, please feel free to let me know.
Best Regards,
Community Support Team _ Eason
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |