Merge 2 column in Direct Query

[Anonymous]

I have 2 table tbl1 & tbl2. Both the table have "ProductID" & "ProductVersion" column. For making the relationship between both the tables, I need to link "ProductID" & "ProductVersion" columns in both the tables and both the columns contains duplicate value.

I am using Power BI in Direct Query mode.

Solution that I am trying is that create a column by merging "ProductID" & "ProductVersion" in both the tables but I am not able to merge column in Direct Query mode.

I have also tried creating a new column "Product_ID = tbl1[product_key]&" "&tbl2[product_version]"

But that way too I am getting an error which says "we couldn't fold the expression to the data source. Please try a simpler expression."

Please help me.

Thanks!!!

[v-yuezhe-msft]

@Anonymous,

I think that you DAX shoud be as follows.

Product_ID = tbl1[product_key] & " " & tbl1[product_version]

Do you use the latest version of Power BI Desktop? I make a test using the above similar DAX in DirectQuery mode in January update of Power BI Desktop, everything works well.


Regards,
Lydia

[Anonymous]

Hi,

I am using version 2.51 for my development purpose in which the provided DAX query is not working.

I have also tried Version 2.54 (January release) but I want to exclude that version for the discussion in thread because Direct Query in 2.54 version is not working with my DB i.e. Vertica.

Thanks!!!

[v-yuezhe-msft]

@Anonymous,

I have no Vertica environment, I make a test using SQL Server database and the DAX works well. 

You are using October update of Power BI Desktop, have you tried November and December versions of Power BI Desktop?

Regards,
Lydia

[Anonymous]

Hi,

I have tried December release. After implementing the DAX, I am getting below error which reading the data from the derived column:

Feedback Type:
Frown (Error)

Timestamp:
2018-01-18T09:04:54.3153927Z

Local Time:
2018-01-18T01:04:54.3153927-08:00

Session ID:
26652ce3-680b-428f-a19e-1fa68cb21596

Release:
December 2017

Product Version:
2.53.4954.621 (PBIDesktop) (x64)

Error Message:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..

OS Version:
Microsoft Windows NT 6.3.9600.0 (x64 en-US)

CLR Version:
4.5.1 or later [Release Number = 378675]

Peak Virtual Memory:
1.76 GB

Private Memory:
353 MB

Peak Working Set:
511 MB

IE Version:
11.0.9600.17416

User ID:
90790e84-5fec-447e-960d-51eb1038b831

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
DirectQuery

Snapshot Trace Logs:
C:\Users\Administrator\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1508616082.zip

Performance Trace Logs:
C:\Users\Administrator\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_daxTemplatesEnabled
CustomConnectors
PBI_reportBookmarks
PBI_AdobeAnalytics
PBI_qnaExplore

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

Activity ID:
null

Time:
Thu Jan 18 2018 01:04:43 GMT-0800 (Pacific Standard Time)

Error Code:
QueryUserError

OData Error Message:
Failed to execute the DAX query.

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared online_sales_fact = let
Source = Vertica.Database("<IP Address>", "VMart", []),
online_sales_Schema = Source{[Name="online_sales",Kind="Schema"]}[Data],
online_sales_fact_Table = online_sales_Schema{[Name="online_sales_fact",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(online_sales_fact_Table,{"product_key", "product_version", "sales_quantity"})
in
#"Removed Other Columns";

shared product_dimension = let
Source = Vertica.Database("<IP Address>", "VMart", []),
public_Schema = Source{[Name="public",Kind="Schema"]}[Data],
product_dimension_Table = public_Schema{[Name="product_dimension",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(product_dimension_Table,{"product_key", "product_version", "product_description"})
in
#"Removed Other Columns";

[v-yuezhe-msft]

@Anonymous,

Seems that this is a limitation for Vertica in Directquery mode. Is there any possibility that you add required columns in the Vertica source, then click refresh button in Power BI Desktop to get the new columns?

Regards,
Lydia

[Anonymous · Accepted solution]

Hi,

I have waited for the Feb release before making my point on this thread and now as the Feb release has arrived, I have tried my hands on it.

It too contains the issue of concatenating columns using Direct Query Mode which I have reported to them and hope that this will be fixed in March release.

http://community.powerbi.com/t5/Issues/concatenation-of-column-not-working-in-Direct-Query-with-vert...

Though I know that merging columns in DB will work in PBI, I am not going to do it because I don't think that will be a good idea.

Thanks for having discussion with me on this Thread. I enjoyed getting idead from you.