Semantics of DAX Queries & Caveats to Composite Models (with Jeffrey Wang)

The popularity of Power BI has increased dramatically in the past few years. I am seeing an increasing number of enterprise customers who built complex composite models combining import tables with DirectQuery tables. Many users have asked questions on how DAX queries and measures are translated into remote SQL/MDX/DAX queries. To the surprise of a lot of people who have a SQL background, the semantics of DAX queries is very different from that of SQL queries even though both are used to produce the right data for the same visualizations. I am going to explain why the semantics of DAX queries poses unique challenges to DAX engine and how the latter employs myriads of optimizations to deliver good query performance in common scenarios. I am also going to explain how the semantics of DAX queries complicates query generations for composite models, demonstrate some of the issues most frequently encountered by the composite model users, and describe design principles to avoid the pitfalls.
GUEST BIO 👤
Jeffrey joined Microsoft SQL Server Analysis Services team in 2004 and contributed to the revolutionary transformation of Microsoft BI from multi-dimensional model and MDX language to tabular model and DAX language. He was one of the inventors of the DAX programming language in 2009 and have been driving the evolution of the DAX language ever since. Currently he is an engineering manager focusing on the development of DAX engine, query optimizer, DirectQuery, composite models, etc. Right now his team is putting the finishing touches on the GA of DirectQuery to PowerBI datasets.
RELATED CONTENT 🔗
Jeffrey's LinkedIn -- www.linkedin.com/in/jeffrey-y-wang
Jeffrey's Twitter -- / jwang_pbi
Jeffrey's Blog -- pbidax.wordpress.com
LET'S CONNECT! 🧑🏽‍🤝‍🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
HAVENS CONSULTING PAGES 📄
Home Page - www.havensconsulting.net
Blog - www.havensconsulting.net/blog-...
Blog Files - www.havensconsulting.net/blog-...
Files & Templates - www.havensconsulting.net/files...
Consulting Services - www.havensconsulting.net/consu...
Contact & Support - www.havensconsulting.net/conta...
EMAIL US AT 📧
[e-mail]
#PowerBI #powerplatform #microsoft #businessintelligence #datascience #data #dataanalytics #excel #powerapps #datavisualization #dashboard #bi #analytics #dax #compositemodels

[@samaguire1]

Really good session! Thanks for getting Jeffrey on the channel.

[@alt-enter237]

This was simply incredible! I will listen to JeffreyWang read the phone book-he’s that good!!!

[@HavensConsulting]

He's fantastic for sure!

[@cannanam]

This is a great video and a great conversation. For those of us who have fallen in love with DAX, the succinct walkthrough of the SUMMARIZECOLUMNS semantics and solution sequence crystallizes why (once you learn DAX) the “mental model simplifies” down to the cross-join space and a set of filters… at which point you run your arbitrarily complex measures row-by-row. Wow! You can understand and explain any PBI visual behavior with that one slide! Thanks for producing this video!!

[@HavensConsulting]

You're welcome!

[@GregKramerTenaciousData]

must see tv....Thanks Reid! Thanks Jeffrey!

[@jamesvaneaton]

High Quality Content™

[@AHMEDALDAFAAE1]

In Power BI, is this statement correct?
In SUMMARIZECOLUMNS, the filter context will ignore any expansion columns in the filtering section of SUMMARIZECOLUMNS. Please explain.
One more note, the dax query that is generated by visuals combines all external filters into one big filter. (you have 2 filters in your example)
Thank you for this great video and for answering my questions! :)

[@HavensConsulting]

Great question! It's in depth enough that I'd recommend posting this to the Power BI community forums so other people will have more exposure for your answer as well :)
community.fabric.microsoft.com/

[@AHMEDALDAFAAE1]

@@HavensConsulting Thank you 😊 I will

[@AHMEDALDAFAAE1]

I did my own validation and I found SUMMARIZECOLUMNS can use the extension table for filtering using SUMMARIZE in the filtering section without any issues.
EVALUATE
SUMMARIZECOLUMNS(
Actuals[Class],
Actuals[Location],
CALCULATETABLE(SUMMARIZE(Actuals, 'PS Type'[Type]), 'PS Type'[Type] = "Direct Hire")// PS TYPE is an expansion table of Actuals table
) // This query works exactly as expected where I used the extension columns as a filter

[@HavensConsulting]

@@AHMEDALDAFAAE1 thank you for this information. Really appreciated