Forum Discussion
Filter in calculated column in direct query mode
I have a calculated column which filter data on the basis of other column. the calculation is working fine in import mode of source but failing in direct query mode.
Can anyone please suggest a workaround?
Hi ElevonTeam,
>> the calculation is working fine in import mode of source but failing in direct query mode.
Current there are a few limitations to use calculation in directquery mode, so you can't use calculated column to achieve your requirement.
Reference link:
- 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.
>>Can anyone please suggest a workaround?
In my opinion, I'd like to suggest you use measure to instead.
Regards,
Xiaxoin Sheng
3 Replies
v-caliao-msftMicrosoft Employee
I have create a sample Power BI report which connect to SQL Server database by using DirectQuery mode, and then create a calculate column. I can use this column in Slicer and use it to filter data without any questions.
So could you please provide us more detail information, so that we can make further analysis. You can upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.
Regards,
Charlie Liao
Try recreating the the complete table brought through by direct mode using dax...
Create new table
XTable = SUMMARIZE(Table,Header a, Heade B,...
)
Then add a calclated column to it what you need.
The calculated column on a Direct query table becomes a measure rather than a calculated column
Hi ElevonTeam,
>> the calculation is working fine in import mode of source but failing in direct query mode.
Current there are a few limitations to use calculation in directquery mode, so you can't use calculated column to achieve your requirement.
Reference link:
- 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.
>>Can anyone please suggest a workaround?
In my opinion, I'd like to suggest you use measure to instead.
Regards,
Xiaxoin Sheng