Table Relation Performance: ID (with lookupvalue) vs Text

[Anonymous]

I would like to know what your guys oppinions on a best practice model are?

If I have two tables, say fact and dim that I want to create a relationship with, which one of the two is better:

a) create a Calculated Column in fact-table with lookupvalue on the text column in dim-table and get an ID for the relationship

b) make relationship based on text column between fact-table and dim-table

[v-shex-msft]

Hi @Anonymous,

For power bi data modeling, you can take a look at the official document:

Understand star schema and the importance for Power BI 

For the two methods you mentioned, they should base on your table data amount.
If you are working with a small fact table, you can add a calculated column to lookup value. If you are work with table existed huge amount of records, method b should better for performance.

Optimization guide for Power BI - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng

[sayaliredij]

It depends. Need to see what query is being created for such DAX.  But what I can imagine that it might work in some scenarios but in many scenarios, it will be a problem.

But in this case, I would always try to add a surrogate key in the source. Eg. if it's a database then when you are populating the fact table you can try to add this dimsurrogatekey there. 

[sayaliredij]

It depends on number of rows in your model.

If you are dealing with small model , then joining text columns should be fine

But look up will create more complexity in the model and I think it will not gain any performance benefits. 

It is always better to use integers as relationship keys. which you can either try to create in power query or if you are using any database system then there. 

[Anonymous]

Thanks for your answer. My question is for a large dataset, lets say millions of rows and multiple fact tables potentially.
Power query isnt a fitting option since joining the dim to the fact based on a text column is usually very slow and not feasible. Say you have 3 dim tables, it doesnt make sense to join all these into fact just for numeric key.
Does that mean using lookupvalue to get id for relationship is never a good option with big models?