Direct Query and Add Column->Custom Function

[Jorgen]

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  

[v-yuezhe-msft]

@Jorgen,

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

Regards,
Lydia

[Jorgen]

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 

[v-yuezhe-msft · Accepted solution]

@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