Model View Compound Key Relationship

[LiquidMetal]

I have loaded two tables into Power BI Service.

I will need to join the two tables with two fields from each table.  So far, this seems like this is not possible.  Therefore, I have joined the two fields in the transact SQL and created a single field.  This works but is silly.  Anyway, in the model view, is there a way to do a compound key join?

I know that I can do a merge with DAX to do a compound join in power query.  Problem is it seems that it will take up space and impact performance.  I could be wrong.  Feel free to correct me on this assumption.

[Ritaf1983 · Accepted solution]

Hi @LiquidMetal 
There are a few methods to merge tables by multiple columns :
1. To concatenate columns an create a relationship by concatenated column (like you did on SQL)
2. Ro merge tables to one by multiple from PQ

3. Using TREATAS DAX Function.
For more details please refer to the linked video:
https://www.youtube.com/watch?v=WrwTCbKxG7U&t=860s
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[LiquidMetal]

Rather than creating the join at the Model view in MS Power BI, would it be better to just join at the T-SQL level?

[Ritaf1983]

You are right again 🙂 

The rule is all processes that you can do on source , just do there .

[LiquidMetal]

I have accepted your solution.  THanks

But just to confirm, so there is no way of creating compound joins in the Model View right?  It has to be through the above methods?  Also, method 2 will probably take up space and performance since it has to generate a new table.

[Ritaf1983 · Accepted solution]

Hi @LiquidMetal 

1. Right.

2 Every solution has its perfomance costs...

You can check them with dax studio

[Ritaf1983 · Accepted solution]

Hi @LiquidMetal 
There are a few methods to merge tables by multiple columns :
1. To concatenate columns an create a relationship by concatenated column (like you did on SQL)
2. Ro merge tables to one by multiple from PQ

3. Using TREATAS DAX Function.
For more details please refer to the linked video:
https://www.youtube.com/watch?v=WrwTCbKxG7U&t=860s
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly