Power Query JOIN vs LookUpValue / Calculated Column DAX

[Anonymous]

Hello Community,

I want to implement a Slowly Changing Dimension (SCD) table to a data model with several large fact tables.

For this I need the surrogate key to be in all fact tables. What is the best way to achieve this?

I have tried joining the scd table in all fact tables in power query but this is painfully slow.

An other option is to use Lookupvalue but I need to filter by more than one "key" fur the surrogate, so I will do a calculated column with a filter on the scd table.

What do you recomend performance wise?

Greeetings

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

As far as I am concerned, using LookUpValue or calculated columns in the Power BI is not a good way to achieve this. Because it will take up memory in the dataset and consume a lot of resources when the dataset refreshed in the service.

As a result, finishing the operations in the data source can be the best choice if it’s possible, If not, you can try to achieve this in the Power query, it can also avoid a lot of resource consumption.

Comparing DAX calculated columns with Power Query computed columns

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mhossain]

@Anonymous 

If your source is relational DB, you can do these steps in source will be better. If not an option, then powerquery is better option I think in your case, however it will be slow if many merges and huge datasets.

From which source your getting data into your powerbi?