Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I need help removing both duplicate and not identical time values based on same code, name and type, then reporting the maximum time elapsed here is my data sample
Code | Name | Start Date | End Date | Time Elapsed | Type |
KA | AA | 20/07/2021 8:24 | 20/07/2021 11:28 | 184 | RR |
KA | AA | 20/07/2021 8:27 | 20/07/2021 11:26 | 179 | RR |
KA | AA | 20/07/2021 8:37 | 20/07/2021 11:18 | 161 | RR |
KA | AA | 20/07/2021 8:40 | 20/07/2021 11:16 | 156 | RR |
KA | AA | 20/07/2021 11:30 | 20/07/2021 15:55 | 265 | RR |
KA | AA | 20/07/2021 11:30 | 20/07/2021 15:55 | 265 | RR |
KA | NN | 29/08/2021 10:06 | 29/08/2021 10:35 | 29 | RR |
KA | NN | 29/08/2021 10:06 | 29/08/2021 10:35 | 29 | RR |
KA | NN | 29/08/2021 12:22 | 29/08/2021 13:26 | 64 | RR |
KA | NN | 29/08/2021 12:22 | 29/08/2021 13:26 | 64 | RR |
KA | NN | 29/08/2021 12:22 | 29/08/2021 13:25 | 63 | RR |
KA | MM | 15/07/2021 12:07 | 15/07/2021 13:10 | 63 | RR |
KA | MM | 15/07/2021 12:07 | 15/07/2021 13:10 | 63 | RR |
KA | MM | 15/07/2021 14:14 | 15/07/2021 14:51 | 37 | RR |
KA | MM | 15/07/2021 14:15 | 15/07/2021 14:44 | 29 | RR |
KA | MM | 15/07/2021 14:15 | 15/07/2021 14:51 | 36 | RR |
KA | MM | 15/07/2021 16:01 | 15/07/2021 20:06 | 245 | RR |
KA | MM | 15/07/2021 16:01 | 15/07/2021 20:05 | 244 | RR |
KA | MM | 15/07/2021 16:01 | 15/07/2021 19:49 | 228 | RR |
KA | MM | 15/07/2021 20:15 | 15/07/2021 20:34 | 19 | RR |
KA | MM | 15/07/2021 20:15 | 15/07/2021 20:32 | 17 | RR |
This the outcome I am looking for
Code | Name | Start Date | End Date | Time Elapsed | Type |
KA | AA | 20/07/2021 8:24 | 20/07/2021 11:28 | 184 | RR |
KA | AA | 20/07/2021 11:30 | 20/07/2021 15:55 | 265 | RR |
KA | NN | 29/08/2021 10:06 | 29/08/2021 10:35 | 29 | RR |
KA | NN | 29/08/2021 12:22 | 29/08/2021 13:26 | 64 | RR |
KA | MM | 15/07/2021 12:07 | 15/07/2021 13:10 | 63 | RR |
KA | MM | 15/07/2021 14:14 | 15/07/2021 14:51 | 37 | RR |
KA | MM | 15/07/2021 16:01 | 15/07/2021 20:06 | 245 | RR |
KA | MM | 15/07/2021 20:15 | 15/07/2021 20:34 | 19 | RR |
Solved! Go to Solution.
Hi
You should remove real duplicate in Power Query and then working in DAX to create a calculate column like a flag to filter your data
Flag =
var MaxEnd = CALCULATE( MAX('Table'[End]),FILTER('Table',EARLIER('Table'[Name])='Table'[Name]))
var MinStart = CALCULATE( MIN('Table'[Start]),FILTER('Table',EARLIER('Table'[Name])='Table'[Name]))
return
IF(
MaxEnd ='Table'[End],"X",
if(
MinStart = 'Table'[Start],"X"
)
)
You will get this result
Hi
You should remove real duplicate in Power Query and then working in DAX to create a calculate column like a flag to filter your data
Flag =
var MaxEnd = CALCULATE( MAX('Table'[End]),FILTER('Table',EARLIER('Table'[Name])='Table'[Name]))
var MinStart = CALCULATE( MIN('Table'[Start]),FILTER('Table',EARLIER('Table'[Name])='Table'[Name]))
return
IF(
MaxEnd ='Table'[End],"X",
if(
MinStart = 'Table'[Start],"X"
)
)
You will get this result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |