Hello community,
Here is some sample data:
| id | tripid | Value | Value2 |
| 1 | 1 | 2,1 | 5 |
| 2 | 1 | 2,1 | 7 |
| 3 | 1 | 2,1 | 3 |
| 1 | 2 | 3,7 | 2 |
| 2 | 2 | 3,7 | 6 |
| 3 | 2 | 3,7 | 8 |
| 1 | 3 | 1,1 | 4 |
| 2 | 3 | 1,1 | 2 |
| 3 | 3 | 1,1 | 5 |
| 4 | 3 | 1,1 | 8 |
| 1 | 4 | 0,7 | 9 |
| 2 | 4 | 0,7 | 4 |
| 3 | 4 | 0,7 | 32 |
| 1 | 5 | 0,9 | 4 |
| 2 | 5 | 0,9 | 65 |
I am trying to sum all of the values in the [Value] column but only once for every TripId. My current measure looks like this:
SUMX(VALUES('Table'[TripId]), CALCULATE(MAX('Table'[Value])))
I have been getting some performance issues with this measure unfortuately. I've tried doing some basic tests where I just put Year, Month and the measure in a visual
| Period | MS | Rows in table in selection |
| Single year | 650 | 5347756 |
| Two years | 1487 | 12138754 |
| Three years | Visual has exceeded the available ressources | 19318853 |
I get how iterating over the table is bad for performance vs a simple sum, but I have so few rows to work with (~33 m rows total) so it just feels like I must be missing something obvious since I am getting such bad performance from a simple measure.
Are there any tricks that I can use or a different DAX pattern perhaps? My only idea is adding aggregation tables, but it just seems excessive for something so basic and with so small data volumes
Note that my calendar dimension is filtering this fact table through a date key and has a one-to-many relationship.
If I do a simple EVALUATE on just the measure, grouped by nothing, the measure runs in 1 MS for all of my data in DAX Studios.
The following query in DAX Studios runs in 2900 MS:
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
VALUES( 'Calendar'[Year] ),
"Value", [Measure]
)
)
2900 MS is obviously a very slow measure, but I am surprised that it cannot run at all, not even locally in desktop.
