How to use a custom SQL function in a DirectQuery/PowerQuery?

Anonymous — Thu, 07 Sep 2023 16:20:12 GMT

In our Power BI dashboard, we want a simple visualization as explained below.

The table consists of two columns: a date and the temprature that was measured on that date

A user has to select a time range, being a begin and end date. e.g. 01/01/2022 and 01/01/2023

Next we want to aggregate the temperatures within this range. We are talking about a custom aggregate function that is not one of the standard aggregation functions (like average, median). We want to do something more complex such as calculating the linear regression coefficients.

The function to calculate the linear regression coefficients has been implented by us in SQL and is registered in the underlying database. Eventually leading our query to be something like: SELECT linear_regression_coefs(temprature) in MyTable where date > start_date_param AND date end_date_param

Now, how am I able to implement this query and use my function in Power BI?

Re: How to use a custom SQL function in a DirectQuery/PowerQuery?

lbendlin — Sun, 10 Sep 2023 00:57:11 GMT

There are a few blog entries for that, for example Chris Webb's BI Blog: TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery (crossjoin.co.uk)

topic Re: How to use a custom SQL function in a DirectQuery/PowerQuery? in Developer

How to use a custom SQL function in a DirectQuery/PowerQuery?

Re: How to use a custom SQL function in a DirectQuery/PowerQuery?