Forum Discussion

mverwil's avatar
mverwil
Frequent Visitor
2 years ago
Solved

Compute calculation without refreshing whole dataset

I have a large dataset in PowerBI Service, that was fetched from a web API. It is separated in multiple partitions (one per month) and there is an incremental refresh setup once per day. It only refr...
Need Help
  • mverwil's avatar
    mverwil
    2 years ago

    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 message

    The 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.

Jonvoge's avatar
Jonvoge
Most Valuable Professional
2 years ago

Hi mverwil

 

Is there a specific reason why you can't use Calculated Measures, instead of Calculated Columns?

They are both more dynamic in nature, and are only computed once the measure is used in a visualization.

 

Measures in Power BI Desktop - Power BI | Microsoft Learn

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

mverwil's avatar
mverwil
Frequent Visitor
2 years ago

Hi Jonvoge, 

 

Thanks for your answer.
Maybe I misunderstand something, but I was under the impression that measures are used for aggregations. So I would use a measure to show the SUM of the prices for example. What I want is to have, for each row, the difference between two fields of this row.

I did try to create a Measure, but I got this error:

 

Calculation error in measure 'Table'[timeDifference]: A single value for column 'Column1.endDate' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

I also tried to have directly a measure for the average by doing this 

AVERAGE(Table[Column1.endDate] - IncidentsData[Column1.startDate])  

 But it said

 

The AVERAGE function only accepts a column reference as an argument.

 

So it seems I need to create both a Calculated column for the difference, and a Measure for the average of this calculated column.