Bridge Table - Do in PowerQuery or in Dax?

[Anonymous]

Hi

I'm creating a bridge table, but I'm unsure about if this must be done in PowerQuery thought a Reference or thought a DAX expression.

If I create it thought a DAX expression, it's very quickly.

If I create it thought PowerQuery, using reference table, at least the first time, it takes a lot of time because it reloads the whole table from server.. (No idea why..)

What is the best option?

As far as I know, if created in PowerQuery, the optimization of the in-memory table is better, because it can compress data, while in DAX no.

Thanks!

[v-yuta-msft · Accepted solution]

@Anonymous ,

I've found several third-party blogs which may help you:

https://radacad.com/m-or-dax-that-is-the-question

https://technologyblog.rsmus.com/microsoft-dynamics/dynamics-365-for-finance-and-operations/difference-m-dax-in-power-bi/

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

Community Support Team _ Jimmy Tao

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

[mwegener]

Hi @Anonymous ,

check this Blog Post from Chris Webb

Why Does Power BI Query My Data Source More Than Once?

https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/

Maybe it helps.

[Greg_Deckler]

In general, the more that you can move data manipulation up the "food chain" towards the source the considered the better way to go. That being said, DAX makes it super convenient to do these kinds of things. Also, to your point about efficiency, both methods would have the same efficiency in terms of data storage in the model. I would expect that after the first time, the Power Query would be faster than the DAX in terms of loading the data into the model but I have not tested that.