Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JonSwed
Advocate II
Advocate II

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

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?







3 REPLIES 3
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


JonSwed
Advocate II
Advocate II

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
Super User
Super User

@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/

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.