Forum Discussion
Compute calculation without refreshing whole dataset
I have found the solution.
I can actually use Calculated Columns. I thought the whole dataset needed to be refreshed in order for them to be computed, since I was seeing this error messageThe expression referenced column 'MyTable'[My Calculated Column] which does not hold any data because it needs to be recalculated or refreshed.But I've noticed that if I only refresh a single partition (through SSMS), the column is computed for the WHOLE dataset. I can therefore add any Calculated Column I might need, and then use Measure to compute aggregation functions on them.
Hi mverwil，
Based on your description, if you don't want to import the dataset all together to do the calculations, you're better off using MEASUREMENT to do the calculations. This is because measure only needs to perform calculations based on the context. You mentioned that you used incremental refresh, which is a good thing to try. You can dynamically get the max and min dates.
DateDifference = DATEDIFF(MIN(Table[StartDate]), MAX(Table[EndDate]), DAY)
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for your reply! I cannot just have a measure, I need this calculation for each row.
| id | startDate | endDate | duration (Calculated Column) |
| 1 | 01/03/2024 | 05/03/2024 | 4 days |
| 2 | 02/03/2024 | 03/03/2024 | 1 day |
| 3 | 05/03/2024 | 15/03/2024 | 10 days |
I need to have the duration for each row, and then I can add a measure for it. But I have found that the Calculated Column can be computed by refreshing any small partition (the last day), see my answer.