Forum Discussion
CombineValues() truncates second entry - not sure why?
In a direct query model - I'm trying to created a relationship using CombineValues.
The source columns are all type text and appear as follows:
Table: Jobs
[WO] [SU] [SE]
012345 000 123456
The calculated column formula I am using is:
WO_SU = COMBINEVALUES("-",Jobs[WO],Jobs[SU])
The expected result should be 012345-000 but instead it produces 012345-00
If I change the order of WO and SU, then the last character of Jobs[WO] is cut off instead 000-01234
Any ideas why the last character would get truncated when using this formula?
3 Replies
Hi, swise001
I created data to reproduce your scenario in DirectQuery mode with Sql Server. It appears normal.
Here are some Limitations in calculated columns with DirectQuery mode:
Calculated columns are limited to being intra-row, 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, are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used. For further information, please refer the official document .
I'd like to suggest you take a look at the query from data source to see if it is truncated during the query. I used Sql Server Profiler.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Direct query allowed you to create a column ?
Try like = Jobs[WO] & "-" & Jobs[SU]
This query is specifically optimized for creating calculated columns in direct query models.
https://docs.microsoft.com/en-us/dax/combinevalues-function-dax
The order of the syntax is set by the DAX formula, with the delimiter listed first - then the columns.
