PowerBI - Join DirectQuery and Imported tables in DAX

1
votes

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.

1
joinpowerbipowerquery

1 Answers

0
votes

The UID field is named differently for each table.

I suspect this may be the issue. NATURALINNERJOIN looks for matching column names and if the two tables have no common column names, an error is returned.

Note that if you create a calculated DAX table using a DirectQuery source, I don't think that table will still act like DirectQuery. If I understand correctly, it will materialize the calculated table into your model and DAX that references that calculated table no longer points back to the SQL server (and consequently will only update when the calculated table gets rebuilt).