Join DirectQuery and Imported tables in DAX

Question

I have a DirectQuery table (Weather) which is sourced from an Azure SQL server. I would like to join this with an Imported table (Buckles) from an Excel sheet sourced from SharePoint Online.

Both tables have a UID field that is made up of a concatenation between a SiteID and timestamp. The UID field is named differently for each table.

I have created a One-To-Many relationship between the two tables.

I have tried to create a new DAX table using a NATURALINNERJOIN on Weather and Buckles but I get this error:

"No common join columns detected. The join function 'NATURALINNERJOIN' requires at-least one common join column."

I am confident it is not a problem with the underlying data because I've created a new imported Excel table (Test) with a selection of the data from Weather and I'm able to successfully create the join on Test and Buckles.

Is the joining of DirectQuery and Imported tables supported? I feel like this may be a type casting issue, but as far as I can see, both UID fields are set as Text.

amitchandak · Answer

Anonymous , just check you join is Active. Also, check there no space in the names.
&nbsp;
Refer this : https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
&nbsp;
&nbsp;

anonymous · Answer

When you say the join is active, do you mean the relationship? I think that's already the case.

Definitely no space in the names.

I've reviewed the link but it doesn't really give any more details. I'm following the guidance by creating a relationship between the relevant fields and then using the NATURALINNERJOIN on a new DAX table

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,Please mark sure the relationship has been created between Weather and&nbsp;Buckles table based on field UID first, then used the method as the link provided by&nbsp;amitchandak to create the new table as below:NewTable=
VAR buckles_TreatAs =
    TREATAS ( Weather, Buckles[UID], Weather[field1], Weather[field2] )
VAR Result =
    NATURALLEFTOUTERJOIN ( buckles_TreatAs, Buckles)
RETURN
    ResultBest RegardsRena

anonymous · Answer

Hi,&nbsp;So I've tried your solution:&nbsp;Merge2 = 
 VAR buckles_TreatAs =
    TREATAS ( Weather, 'Buckle Data'[UID_Date], Weather[DateTime], Weather[DateTime - Copy], Weather[Radiation], Weather[RelativeHumidity], Weather[SMILevel1], Weather[SMILevel2], Weather[SMILevel3], Weather[SMILevel4], Weather[Snowfall], Weather[Temperature], Weather[TotalPrecipitation], Weather[WeatherCell], Weather[WeatherCell - Copy], Weather[WindDirection], Weather[WindGust], Weather[WindSpeed] )
VAR Result =
    NATURALINNERJOIN ( buckles_TreatAs, 'Buckle Data')
RETURN ResultMy Buckles table is actually called 'Buckles Data' in my model.As far as I understand, I need to supply the TREATAS function all of the columns in the Weather table, these are a mix of data types.I now get the following error when trying to run the function:&nbsp;Cannot convert value '2367:Apr 2 2006 3:00PM' of type Text to type Integer while processing column - Weather[UID_Weather] -&gt; Weather[WindSpeed].The value shown here is the first UID value from the Buckles Data table. I'm not sure why it's trying to convert it to an integer, nor why it's processing data between the UID_Weather and WindSpeed columns.I have the relationship set up as One-to-Many between Weather[UID_Weather] and 'Buckles Data'[UID_Date]

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,Could you please provide some sample data both in Weather and Buckle Data table in form of screen shot or table(include fields and data) in order to make troubleshooting on data convert issue? Thank you.Best RegardsRena

Forum Discussion

Join DirectQuery and Imported tables in DAX

8 Replies

Related content

Splitting tables between directquery and import mode

Measures breaking after switching source from DirectQuery to Import (table relationships?)

Filtering DirectQuery table based on import mode table - visuals

Filtering DirectQuery table based on import mode table

Should I use DirectQuery or Import Option?