Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table that has snapshot data. There are multiple snapshots each day. I need to create current state measures that only take into account the latest snapshot. I'm wondering, regarding performance, is the best way to address this by creating measures that filter by the latest date and time or create a duplicate table that removes duplicates (Category) and only keeps the latest snapshot? Keep in mind, if I do the latter, I'll have to do this for multiple tables in my data model. Example data is below.
Date | Time | Category | Cost |
8/4/2018 | 00:16:55 | Group 1 | 563180 |
8/4/2018 | 01:14:27 | Group 1 | 563180 |
8/4/2018 | 02:20:15 | Group 1 | 563180 |
8/4/2018 | 03:14:43 | Group 1 | 563180 |
8/4/2018 | 04:13:56 | Group 1 | 563180 |
8/4/2018 | 05:15:28 | Group 1 | 563180 |
8/4/2018 | 06:14:09 | Group 1 | 563180 |
8/4/2018 | 07:14:23 | Group 1 | 563180 |
8/4/2018 | 08:15:31 | Group 1 | 563180 |
8/4/2018 | 09:13:59 | Group 1 | 563180 |
8/4/2018 | 10:13:34 | Group 1 | 563180 |
8/4/2018 | 11:14:48 | Group 1 | 563180 |
8/4/2018 | 12:15:48 | Group 1 | 563180 |
8/4/2018 | 13:14:25 | Group 1 | 563180 |
8/4/2018 | 14:14:17 | Group 1 | 563180 |
8/4/2018 | 15:13:27 | Group 1 | 563180 |
8/4/2018 | 16:15:04 | Group 1 | 563180 |
8/4/2018 | 00:16:55 | Group 2 | 8060 |
8/4/2018 | 01:14:27 | Group 2 | 8060 |
8/4/2018 | 02:20:15 | Group 2 | 8060 |
8/4/2018 | 03:14:43 | Group 2 | 8060 |
8/4/2018 | 04:13:56 | Group 2 | 8060 |
8/4/2018 | 05:15:28 | Group 2 | 8060 |
8/4/2018 | 06:14:09 | Group 2 | 8060 |
8/4/2018 | 07:14:23 | Group 2 | 8060 |
8/4/2018 | 08:15:31 | Group 2 | 8060 |
8/4/2018 | 09:13:59 | Group 2 | 8060 |
8/4/2018 | 10:13:34 | Group 2 | 8060 |
8/4/2018 | 11:14:48 | Group 2 | 8060 |
8/4/2018 | 12:15:48 | Group 2 | 8060 |
8/4/2018 | 13:14:25 | Group 2 | 8060 |
8/4/2018 | 14:14:17 | Group 2 | 8060 |
8/4/2018 | 15:13:27 | Group 2 | 8060 |
8/4/2018 | 16:15:04 | Group 2 | 8060 |
8/4/2018 | 00:16:55 | Group 3 | 1689792.06 |
8/4/2018 | 01:14:27 | Group 3 | 1689792.06 |
8/4/2018 | 02:20:15 | Group 3 | 1689792.06 |
8/4/2018 | 03:14:43 | Group 3 | 1689792.06 |
8/4/2018 | 04:13:56 | Group 3 | 1689792.06 |
8/4/2018 | 05:15:28 | Group 3 | 1689792.06 |
8/4/2018 | 06:14:09 | Group 3 | 1689792.06 |
8/4/2018 | 07:14:23 | Group 3 | 1689792.06 |
8/4/2018 | 08:15:31 | Group 3 | 1689792.06 |
8/4/2018 | 09:13:59 | Group 3 | 1689792.06 |
8/4/2018 | 10:13:34 | Group 3 | 1689792.06 |
8/4/2018 | 11:14:48 | Group 3 | 1689792.06 |
8/4/2018 | 12:15:48 | Group 3 | 1689792.06 |
8/4/2018 | 13:14:25 | Group 3 | 1689792.06 |
8/4/2018 | 14:14:17 | Group 3 | 1689792.06 |
8/4/2018 | 15:13:27 | Group 3 | 1689792.06 |
8/4/2018 | 16:15:04 | Group 3 | 1689792.06 |
8/4/2018 | 00:16:55 | Group 4 | 74360 |
8/4/2018 | 01:14:27 | Group 4 | 74360 |
8/4/2018 | 02:20:15 | Group 4 | 74360 |
8/4/2018 | 03:14:43 | Group 4 | 74360 |
8/4/2018 | 04:13:56 | Group 4 | 74360 |
8/4/2018 | 05:15:28 | Group 4 | 74360 |
8/4/2018 | 06:14:09 | Group 4 | 74360 |
8/4/2018 | 07:14:23 | Group 4 | 74360 |
8/4/2018 | 08:15:31 | Group 4 | 74360 |
8/4/2018 | 09:13:59 | Group 4 | 74360 |
8/4/2018 | 10:13:34 | Group 4 | 74360 |
8/4/2018 | 11:14:48 | Group 4 | 74360 |
8/4/2018 | 12:15:48 | Group 4 | 74360 |
8/4/2018 | 13:14:25 | Group 4 | 74360 |
8/4/2018 | 14:14:17 | Group 4 | 74360 |
8/4/2018 | 15:13:27 | Group 4 | 74360 |
8/4/2018 | 16:15:04 | Group 4 | 74360 |
8/4/2018 | 00:16:55 | Group 5 | 229500 |
8/4/2018 | 01:14:27 | Group 5 | 229500 |
8/4/2018 | 02:20:15 | Group 5 | 229500 |
8/4/2018 | 03:14:43 | Group 5 | 229500 |
8/4/2018 | 04:13:56 | Group 5 | 229500 |
8/4/2018 | 05:15:28 | Group 5 | 229500 |
8/4/2018 | 06:14:09 | Group 5 | 229500 |
8/4/2018 | 07:14:23 | Group 5 | 229500 |
8/4/2018 | 08:15:31 | Group 5 | 229500 |
8/4/2018 | 09:13:59 | Group 5 | 229500 |
8/4/2018 | 10:13:34 | Group 5 | 229500 |
8/4/2018 | 11:14:48 | Group 5 | 229500 |
8/4/2018 | 12:15:48 | Group 5 | 229500 |
8/4/2018 | 13:14:25 | Group 5 | 229500 |
8/4/2018 | 14:14:17 | Group 5 | 229500 |
8/4/2018 | 15:13:27 | Group 5 | 229500 |
8/4/2018 | 16:15:04 | Group 5 | 229500 |
Solved! Go to Solution.
Hi iDataDrew,
To achieve your requirement, create a measure using DAX as below:
Max Time = CALCULATE(MAX(Table1[Time]), FILTER(ALLEXCEPT(Table1, Table1[Category]), Table1[Date] = MAX(Table1[Date]) && Table1[Time] = MAX(Table1[Time])))
Regards,
Jimmy Tao
Hi iDataDrew,
To achieve your requirement, create a measure using DAX as below:
Max Time = CALCULATE(MAX(Table1[Time]), FILTER(ALLEXCEPT(Table1, Table1[Category]), Table1[Date] = MAX(Table1[Date]) && Table1[Time] = MAX(Table1[Time])))
Regards,
Jimmy Tao
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |