cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jorgen Frequent Visitor
Frequent Visitor

Direct Query and Add Column->Custom Function

Hi,

I'm trying to use custom functions and parameters to dynamically load data from SQL Server with DirectQuery. The problem is that when I try to add a column to a query created with DirectQuery I get the message "This step results in a query that is not supported in DirectQuery mode".

Is this a limitation for DirectQuery? Is there any workaround for this?

 

Thanks and best regards

Jorgen  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Direct Query and Add Column->Custom Function

@Jorgen,

I can reproduce your issue. This is a limitation of DirectQuery mode, I am afraid that you need to switch to Import mode to make the function work.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: Direct Query and Add Column->Custom Function

@Jorgen,

Could you please share the function you create and describe more details about the parameter?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jorgen Frequent Visitor
Frequent Visitor

Re: Direct Query and Add Column->Custom Function

Hi, 

I connect to sql server with DirectQuery and add a query like "select value from table where value = param", the parameter is a simple text value created with New Parameter (type=text, suggested value=any, current value=some value)

I use create function to convert this query to a function.

Then I connect to another table in the same sql server db which contains the parameters.

I want to add a column with the the custom function to the parameter table so every parameter can trigger the query and retreive the values for the particular parameter.

 

The problem is when I select Add Column -> Invoke Custom Function, I get the error message: "This step results in a query not supported in DirectQuery mode". Adding a custom column with no function seem to work fine, at least no error message.

 

Thanks,

Jørgen 

Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Direct Query and Add Column->Custom Function

@Jorgen,

I can reproduce your issue. This is a limitation of DirectQuery mode, I am afraid that you need to switch to Import mode to make the function work.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.