COMBINEVALUES in Databricks/Spark Direct Query cannot be folded

[timokrnmr]

Hi,

im trying to create a calculated Column which combines two columns to create a composite key, so i can use relations in my model.
I am using Apache Spark direct query mode to connect to Databricks data.
I have found some previous posts about this issue but they were all pretty old and none of the recommended Workarounds worked for me.

My Current Attempt is this:

CompositeKey = COMBINEVALUES("-",'iotstreaming_braketest'[uniqueid], 'iotstreaming_braketest'[Timestamp])
Previously also tried to approach this using the CONCATENATE function or the "&" operator, all resulting in the same error.

With my attempts i always get the "OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.." Error message.

I assume Timestamp being a Datetime column could cause some issues.

If there are any solutions to fix my attempts or a completely different approach please let me know.
I dont want to resort to creating the column in my databricks source.

Some sample Data:
uniqueid has the Data type "Text" 
Timestamp has the Data type "Date/time"
uniqueid,Timestamp
H912-321RB-200-KF233.M015SZ31SO414,24.01.2023 00:06:28

H984-185RB-100-KF233.M013SZ20PD311,24.01.2023 00:06:35
H905-036RB-100-KF150.M055SZ20TFCH1,24.01.2023 00:06:48
H923-020RB-100-KF150.M854SZ20THDH1,24.01.2023 00:06:49

[SamInogic]

Hi,

As per our understanding, you want a Calculated Column in Power BI which include combined value from Unique ID and Timestamp column.

We can see in your Given Data the Date/Time is in 24.01.2023 00:06:28

which contains the " . " instead of the /  but the Date Time Format in Power BI do not include any of the format using “.”

So, you need to first convert the current data into correct format by creating a new Calculated column for the Timestamp with Following Dax Expression:

New TimeStamps = SUBSTITUTE('Sample Table'[TIme Stamp],".","/")

Then Create the Desired Column CompositeKey Using the Below Dax Expression,

CompositeKey = COMBINEVALUES("-",'Sample Table'[Unique ID],FORMAT('Sample

Table'[New TimeStamps],"m/d/yy hh:nn:ss"))

This will result in combined values as shown in the below screenshot,

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at [email protected]

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[timokrnmr]

Hi,

Thanks for your answer, however the SUBSTITUTE Function is not available in direct query mode:

I dont believe this was the issue anyways, i think the "." instead of "/" is simply displayed this way because its the German standard, the column datetype is Date/Time in the model Tab:

I know its not easily possible to test with direct query mode but my issue is not reproducable in import mode.