Analyzing a slow report query in DAX Studio

Investigate a slow query in a Power BI report using DAX Studio, looking at the query plan and the server timings. How to learn DAX: www.sqlbi.com/...
DAX Studio: daxstudio.org/
Optimizing DAX Workshop: www.sqlbi.com/...

[@nehachak]

Thanks Marco for this explaining such a critical topic in such simple terms

[@evnpresson7258]

Absolutely fantastic! I can't tell you how much this video helped me to optimize some of my measures which I have been struggling with to make faster!

[@Fernando_Calero]

Excellent explanation Marco, this video should be reproduced in every #PowerBI event, training course, webinar, etc. as a preamble.

[@rickcoterie5700]

As usual, you are a genius! I had a crazy query that someone else wrote and the visual was timing out, I updated to KEEPFILTERS and same results but actually runs now :). Thank you!

[@SteveFirefly777]

Thanks for sharing. PowerBI is such a clunky assembly of tools, I pine for the simplicity and efficiency of Tableau.

[@vijayvizzu1]

Wow, amazing.. really.. you are a genius, i never thought by using DAX studio, we can optimise our query and improve the performance. Really, this was the new way around to use the DAX studio. Thank you for sharing your extensive knowledge on Power BI and DAX.

[@josbar80]

Thank you so much, this improved my visual load times by an unbelievable amount of time

[@csterling81]

love these bite size chunks of DAX learning, thanks

[@mintheinaung9555]

Thanks for sharing great insight and advanced use of query optimization techniques!

[@DanielADamico]

Amazing! Your videos are awesome! Thanks for sharing!

[@waldchiller4695]

This is pure Gold. Thx!

[@galymzhankenesbekov7242]

Thank you for a great video. Could you please tell where to find the list of all best practices combined? For example, I use a function called DistinctCount(), but in one of your videos I find that it is very heavy function for Power BI to execute. Better to use Sumx(values(____),1) (depending on the context of usage).

[@SQLBI]

It depends. We have several articles on SQLBI website (search for optimization), but if you want a complete training look for Optimizing DAX video course: www.sqlbi.com/p/optimizing-dax-video-course/

[@scramiro1]

Great video!

[@syedarshad8500]

Excellent

[@Akn876]

Fantastic

[@craniumnr]

8:36 how do you know when a column is a filter column?

[@zxccxz164]

I have different issue that does not seem solvable by using DAX Studio. SSAS model Dim Customer is 18 million (nothing I can do to fix that as it is used in multiple fact tables) Column in Dim Customer STATUS (Alive, Deceased) Fact table 1 has 4 million rows for this report Linked Fact Customer ID. Dim Customer Customer ID On a PBI report if use flyout filter box and set filter ALIVE, it dramatically slows down output to output to 30 secs. But it is not a measure, so I can’t rewrite it. I know just by using the report that anytime I added filter for status it slows it down, so I already have a head start on issue. If I copy DAX from the visual put in DAX Studio and go to server timings. SET DC_KIND="AUTO"; SELECT 'dim_Customer'[Ent_ID], 'dim_Customer'[Age Current] FROM 'dim_Customer' WHERE 'dim_Customer'[Status] = 'Alive'; It is written behind the scenes as: VAR __DS0FilterTable4 = TREATAS({"Alive"}, 'dim_Customrt'[Status]) Estimated size: rows = 18,395,979 bytes = 73,583,916 CPU 17,344 The only things I know would intuitively work Option 1: put STATUS into the FACT table. I don’t want to go this route. Option 2: have MULTIPLE Customers dimensions for each FACT table would decrease the scan for this particular fact table. This just adds to model complexity and overall size.

[@josuevergara218]

Great!

[@andykebede1133]

It is really awesome video .I use it

[@filecile7363]

Can you show one version of analyzing measure from power pivot. I have couple measures depending on filter context. First thing that is unclear to me is how you can get dax query from measure in power pivot? Second is how to analyze this measure in dax studio, when her calculation is dependent on filter context? I probably missed something, but that is why I have to ask. Every time you want to analyze, you take copy of dax query from power bi, but is it possible from pivot?

[@SQLBI]

You can use "ALL Queries" in DAX Studio, capture the MDX query generated by the PivotTable, and then analyze that in DAX Studio. It is possible, but it adds an additional layer of complexity because of the MDX language used by Excel to query the model. We have some indication about that in the Mastering DAX video course (mixing MDX and DAX queries) on www.sqlbi.com , and you can also use the "Trace MDX Queries" technique explained in the "DAX Tools" video course, in the section about "Analyze in Excel for Power BI Desktop". The technique is the same of Power Pivot. It is also a good idea for future videos. Thanks for the suggestion!

[@sarva0483]

Wow 👏

[@davideperina1377]

Hello Marco, thank you for the video, im struggling with analysis services because i have a server with 6 core but my tabular constantly uses only one core. Is there a way to enable multi core or something? Thanks

[@SQLBI]

Look at how the storage engine works (VertiPaq) vs. the formula engine. You likely have a small database or calculations that are executed in the formula engine instead of the storage engine. Search for optimization related content on SQLBI: www.sqlbi.com/?s=optimization&type=

[@nachomiranda5151]

Great !!! Thanks a lot. What about if FE time is much much bigger than SE time? Where may the problem be? Thanks

[@marcorusso7472]

It depends! Look at our optimization article, videos, and course on www.sqlbi.com

[@Ionesta]

Hello Marco, I am struggling with a performance improvement on a measure build on top of a snapshot fact table containing 2 billion rows. I want to perform a Sum of a value but only in a single day (the last day of the Time context). The formula looks like this: CALCULATE ( SUM ( 'MyTable'[volume] ), FILTER ( 'MyTable', 'MyTable'[Date] = MAX ( 'MyTable'[Snapshot Date] ) ) ) . Based on the example from this video is there a way in which I could rewrite the measure to filter only on column not the entire table? Thanks!

[@marcorusso7472]

See this: www.sqlbi.com/articles/semi-additive-measures-in-dax/ VAR LastSnapshotDate = MAX ( 'MyTable'[Snapshot Date] ) RETURN CALCULATE ( SUM ( 'MyTable'[volume] ), KEEPFILTERS ( 'MyTable'[Date] = LastSnapshotDate ) )

[@Ionesta]

​@@marcorusso7472​, thanks Marco. Spot on!

[@luisfernandomacedo1451]

Hello Marcos! Thanks for sharing this amazing topic! Follow your steps I got stick when tried running Query Plan and Server Timing. I have got a message stating that "Timeout exceeded attempting to start." I increased timeout options, but I am still getting the same error. Can you kindly tell me what going on?

[@SQLBI]

Check on GitHub issues: github.com/DaxStudio/DaxStudio/issues

[@CleitondaSilvaLeal]

Amazing!!

[@israelrescalvo]

Great Content!

[@nimaiahluwalia5678]

Can you provide a link for the best practice of the Calculate DAX which you mentioned in the video at 8:37

[@SQLBI]

Complete discussion: www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/ Useful links: www.sqlbi.com/articles/filter-arguments-in-calculate/ www.sqlbi.com/articles/using-keepfilters-in-dax/

[@DIGITAL_COOKING]

Great one Marco Yes i did understand that filter function for table takes the performance, but i didn't understand the replacement with the keepfilters function

[@marcorusso7472]

Here are a few resources about KEEPFILTERS, you have to understand the behavior of CALCULATE modifying the filter context. www.sqlbi.com/articles/using-keepfilters-in-dax/ www.sqlbi.com/articles/filter-arguments-in-calculate/ A deep explanation of this is also included in our Mastering DAX video course: www.sqlbi.com/p/mastering-dax-video-course/