CombineValues() truncates second entry - not sure why?

[swise001]

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?

[v-alq-msft]

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.

[amitchandak]

Direct query allowed you to create a column ?

Try like =  Jobs[WO] & "-" & Jobs[SU]

[swise001]

@amitchandak 

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.