0

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;

enter image description here

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.

  • 1
    Could you add an schema (CREATE TABLE) and INSERTS just to generate a minimal verifiable example? – McNets Apr 9 '18 at 10:04
  • I will do, hopefully tonight. – SkinnyPete63 Apr 12 '18 at 7:45
0

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

  • Thanks for your response. I am fine with the Power BI piece, it's the best approach to creating efficient SQL that I am struggling with. – SkinnyPete63 Apr 12 '18 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.