Dynamic Function Package

[Rjesak]

Our agency is in the process of moving from Hyperion (Oracle based product) to PowerBI. In Oracle, we have a package called rp which stores hundreds of dynamic PL/SQL functions (for instance, call the staff_function with a date and a location) and it will tell you which staff was assigned as of that date and at that location. Because these functions are dynamic (they're based on a parameter entered by the user), the data can't simply be denormalized. I can see ways of building this information within a Hyperion report but the benefit to having the package is that any report could call that function ensuring that the calculation or lookup is done the same way every time.

Is there another solution that would give us the same abilities? Is there some kind of stored function repository that Power BI could utilize?

Thanks so much for any assistance!

[v-eachen-msft]

Hi @Rjesak ,

I am afraid that Power BI doesn't have a similar function.

You can come up a new idea about that and add your comments here to improve Power BI and make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas

[JasonTX]

You could parameratize this in M query, however, it would involve a bit of M Query scripting if you want to call different functions. If it is just adding parameters to one function it could be done easily by copying the query and entering the concatanating Power BI Paramaters with the query string.

[Rjesak]

We have about a hundred functions to convert. Because these function encapsulate our business rules, we don't want to have to put each function in each report. They're also PL/SQL functions so it's definitely not as straightforward as copying the query because there's a lot of processing going on. The only way I can think of is to maybe use some kind of programming on the back end that could be fed into Power BI...