Multi-column relationship in Direct Query

[amalekshahi]

Hi,

I have two tables in Redshift that I want to connect to using Direct Query, model the data and use it in Power BI. The tables are:

Program: 

   AccountID int

   ProgramID int

   ProgramName varchar (100)

Events

   AccountID int

   ProgramID int

   EventDate TimeStamp

   Description varchar (500)

The tables need to be joined on AccountID and ProgramID. I tried using "Merge Queries" to create a relationship on two columns but Power BI gives me error as this feature isn't supported in Direct Query mode. I saw other suggestions about adding calculated columns to each table but this will have a performance overhead as both columns are integer, need to be converted to string and then concatenated. Is there any other option to create multi-column relationship in Direct Query? 

[v-yulgu-msft]

Hi @amalekshahi,

From my opinion, the better choice is to add a calculated column "AccountID" & "ProgramID" to each table. Then, you can establish relationship between this new column. Why do you mind formatting this new column with string type? This is just a related column, but you can still make calculation based on original numeric fileds.

Regards,

Yuliana Gu

[amalekshahi]

Hi @v-yulgu-msft

I am just concerned about the performance in Power BI, converting int to string and then concatenating them to create relationship seems to be a lot of overhead.

[v-yulgu-msft]

Hi @amalekshahi,

What is your data source type? If using SQL Server Database, another way is to merge table via SQL query. If using Analysis Service, you can merge tables via MDX.

Best regards,

Yuliana Gu