[DAX] Best Practices 101 for Optimization & Performance (with Alberto Ferrari)

Despite being usually very fast, DAX can be optimized, like any other computer system. Optimizing DAX requires a deeper understanding of the internals of the engine, because most optimizations are not intuitive. We will talk about the basics of optimizations, looking at some examples of code and discussing together ideas and options to make the code faster.
GUEST BIO 👤
Alberto started working with SQL Server in 2000 and immediately his interest focused on Business Intelligence. He and Marco Russo created sqlbi.com, where they publish extensive content about Business Intelligence.
Alberto published several books about Analysis Services, Power BI, and Power Pivot. He is a Microsoft MVP and he earned the SSAS Maestro title, the highest level of certification on Microsoft Analysis Services technology.
Today, Alberto's main activities are in the delivery of DAX and data modeling workshops for Power BI and Analysis Services all around the world. Alberto offers consulting services on large and complex data warehouses to provide assessments and validation of project analysis or to perform specific problem-solving activities.
Alberto is a well-known speaker at many international conferences, like PASS Summit, Sqlbits, and Microsoft Ignite. He loves to be on stage both at large events and at smaller user groups meetings, exchanging ideas with other SQL and BI fans. When traveling for work, he likes to engage with local user groups to provide evening sessions about his favorite topics. Thus, you can easily meet Alberto by looking up local Power BI user groups during scheduled courses.
Outside of SQLBI, most of Alberto's personal time is spent practicing video games, in the vain hope of eventually beating his son.
RELATED CONTENT 🔗
SQLBI Website -- sqlbi.com
LET'S CONNECT! 🧑🏽‍🤝‍🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
CHECK OUT OUR MERCH STORE 👕
-- havens-consult...
HAVENS CONSULTING PAGES 📄
Home Page -- www.havensconsu...
Blog -- www.havensconsu...
Blog Files -- www.havensconsu...
Files & Templates -- www.havensconsu...
Consulting Services -- www.havensconsu...
Online Course -- www.havenscons...
Contact & Support -- www.havensconsu...
EMAIL US AT 📧
[e-mail]
#PowerBI #powerplatform #microsoft #businessintelligence #datascience #data #dataanalytics #excel #powerapps #datavisualization #dashboard #bi #analytics #visualbi #dax #datamodeling #tabular #performance #optimizatio

[@businessinsights_AlexRobe]

Awesome session once again, Reid with one of most profound minds in our BI universe! Thanks!

[@HavensConsulting]

It was a blast having him on!

[@martinbubenheimer6289]

Unfortunatelly you can give only one thumb up, I would give a hundred!

[@noahhadro8213]

I have 3 measures. Sales CY, Sales PY, and Sales YoY. Sales YoY Dax is just simply Sales CY - Sales PY. Requirement is these 3 measures each have to be in their own column of a visual table to allow sort. When I try to optimize the Dax using Dax studio I find that the YoY measure is taking the longest naturally. Is there a way to tell that column just to use the queries from CY and PY measures already being sent from the other two columns. I am sending three queries and don’t want to. I only want to send 2. I was really hoping visual calcs would solve this. Essentially I want to only send queries necessary. Then YoY or YoY % I want to simply just use the already run queries. Simply put I want to only query a measure once but then use it in multiple columns in different ways. Any thoughts?

[@HavensConsulting]

Thanks for the comment! A technical question this detailed would be best to place on the Fabric community forums where there's a lot more experts, and you can also provide sample data and files :)
community.fabric.microsoft.com/

[@coolblue5929]

I don’t think that the “other” time has anything to do with the rendering. It’s wait time in the AS model. 🤷🏼

[@HavensConsulting]

The visual display is the rendering. Sorry if that was misquoted. The other does encompass waiting for other visuals to load though! Here's the quote for that from the MSFT Document
Other - This is the time required by the visual for preparing queries, waiting for other visuals to complete, or performing other background processing.