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.
Best Answer
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