Performance question - create a reference table in editor or a calculated table?

[JonSwed]

Hi all!

A performance question.  The queries I'm working with are 10 - 20 millions of rows (BigQuery Google Analytics data mostly).

I've a few dashboards where I've created a 'bridge table' to get around many to many issues between queries. I've been making these in query editor, by creating reference tables from the queries and then appending them to create an unduplicated table. However, this is often quite a time consuming process and power bi takes ages to chew through the various steps. Due to the nature of the data, I often have to lower case it, as well as trimming and cleaning.

I created the same thing using a calculated table with DAX. This seems to work the same, but takes almost no time to calcuate and does not appear to have any appreciable effect on dashboard refresh times. 

Which is best practice? Is using calculated tables unsustainable as, right now, it seems like the better approach?

[mahoneypat]

Using DAX tables is no problem if it better solving your problem.  The only downside is that you have both the query table and the DAX table in your model, so file size is bigger. 

If you'd like to optimize your query, you can share the M code to get potential suggestions from the community.

Regards,

Pat

[JonSwed]

Thanks for that. Another observation is that when the dashboard refreshes the reference tables are refreshing as well (as you point out by saying it creates a copy) so that will definitely slow it down. 
With regard to your comment about star schema - if I create a separate reference table them I am creating a star schema, right?

[amitchandak]

@JonSwed , In power query usually table create a new copy of the old one and do it. So maybe DAX table can solve the issue if that is taking too much time.

Start schema is always better  https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/