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.
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
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 |
---|---|
106 | |
105 | |
79 | |
71 | |
66 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |