Import Query and Direct Query

[Anonymous]

Hi

    I need help to for Direct query.I am using Direct query and I  need to add new column for DAX function Expression can you please me how to use DAX function in Direct Query mode becase Direct query mode data model is not avalable.

[v-qiuyu-msft]

Hi @Anonymous,

From this article, you can see there is a limitation of calculated column in DirectQuery mode: 

• Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Additionally, the DAX scalar functions (such as LEFT()) that are allowed, will be limited to those which can simply be pushed to the underlying source, hence will vary depending upon the exact capabilities of the source. Functions that are not supported will not be listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.

In your scenario, it depends on which logic you would like use for calculated column. If you want to use aggregate functions, eg: Average, Sum which are not supported in DirectQuery. You can try to specify the calculated column in T-SQL query when get data. 

Best Regards,
Qiuyun Yu

[Anonymous]

Hi Qiuyun Yu

          Thank you for your help .But I need to use DAX Function like Date and Time Function,Time Intelligence Fuction,Filter Function,Logical Function this functions using with DirectQuery becase my comapny report requiered calculated function and they wants live data and Data will be update every 15 min thats why I am using Direct Query.

Thanks

[v-qiuyu-msft]

Hi @Anonymous,

Based on my test, most of Date and Time Function,Time Intelligence Function,Filter Function,Logical Function is supported in DirectQuery, you can click New Column button then type desired function, if it's available in prompt dropdown list, then it's supported to use this function. Otherwise, it's not able to use it. 

Best Regards,
Qiuyun Yu

[Anonymous]

Hi

   Qiuyun Yu ,

         Yes, You are right and but I need calculated column in Direct Query mode. year support but I need different function use for my report . I still getting error below image.

Thanks.

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

The DateDiff() function is supported in DirectQuery, please update your desktop to the latest June version then test again. 

Also you can calcualte the date difference in T-SQL queruy as I suggested before: 

Best Regards,
Qiuyun Yu