Best approach to calculation problem

Question

I am re-developing an application that has been running on Excel. One of the reports I need to recreate (which will be published in Power BI and needs to be a direct query) is as follows;

Column "Approved Est" is calculated from a specific table, so is "Rec Value Based on Ordered Works". Column "+/- Approved Est" is a simple calculation based on the first two. This is where i need advice. There are many ways I know of to get the result, and here is what I have considered or tried.

1) Create a function that has a temporary table holding the initial columns so I can run one update that calculates the reliant columns without having to do the select for the original columns again, and ultimately returns one table.

2) Create a view with multiple unions that calculates each column. Down side is that I have to re-run the select for the original columns to give the calculated ones.

3) Create separate functions that return the calculated value for any address

Any advice gratefully received.

Answer 1

For PowerBI DirectQuery you'll need to write a single view that returns all the data. Internally you might use a table-valued function if that is convenient.

If the execution of the queries against the view is too expensive, you'll have to cache the results, either by loading them into a table, or by switching from DirectQuery to Import mode.

David