Debugging a slow Power BI report with Phil Seamark 

Saw a bunch of comments on this. Try not to get caught up in the DISTINCTCOUNT vs. SUMX. The point of the video was the debugging technique. As Phil called out in the video, you may not see it have better performance on your end. Always test and verify. We chatted with Marco Russo as well. He commented that he would start but understanding what DISTINCTCOUNT was performing badly first. Could be due to relationships (maybe bi-direction), etc... the SUMX approach isn't exactly doing the same thing. Which is why mileage will vary.

Phil Seamark is my all time FAV... Glad to see you sir with guyinacube ... I was lucky enough to meet you in person... THANKS A LOT! You are real DAX GURU..

This is great, more and more techniques on troubleshooting is always helpful, nice to see Phil and have a great trip to NL guys

Whoa, that is a sneaky way of fooling a DISTINCTCOUNT, love it! Also, kudos for the SE analogy.

Great video, will help me with some challenges I'm working on now - perfect timing guys. Please keep up the great work!

Guys, this was such a useful tuning 101, huge thanks. I'm slowly but surely getting to grips with Power BI/DAX coming from a SQL Server background. Had no idea you could copy the query from report elements. Thanks again, a really great nugget.

Brilliant !! Thanks for making this available. Eagerly looking forward to more such techniques for better Reporting!! Loved It!! Many Thanks!!

Awesome! Love this. Thank you team.

Pretty awesome, going to definitely try this out. Thanks guys!

Awesome. Thanks Adam and Phil 👍👍

Bro, this was the real magic ...Please keep up the great work!

Thank you for all of your work

This is pure gold! The distinctcount trick was brilliant and helped my reports a good deal!

So glad to hear that off-hand comment about filtering on fact tables over dimensions, that's a pet peeve of mine that I had a gut feeling was a bad idea, I'd just never gone to research it.

Great video. Very helpful for a problem at hand :)

Thanks for this super video. Trying it out now

This was very helpful! Didn't even know PBI has a Performance Analyzer!

Really nice job Phil (and as always, Adam). I loved the analogy about getting cake ingredients one by one. Definitely going to steal that one with pride! Really helpful for providing an overall methodology that can be applied no matter the scenario. As you say below, Adam (or is it above?)--actual results may vary.

You're perfect guys, sumx trick made my visual 4x faster!

Useful technique, thx a lot

Great video 👌💯

very handy lads, thanks

Perfect, thank you!

thats very useful feature.. thankyou guys...

Amazing Video guys. very very informative. Could you guys show how you take that enhanced query from DAX studio and use it in PowerBI.

This is fantastic, more performance videos please! My query went from 12,460 to 121 ms!

Good Stuff! Wish i was at work right now to delve into some of users models. :)

Love the Bifocal shirt as well :D

I love the process, can you please explain how you return the query back to powerbi desktop, you guys are amazing.

Kudos.We need more performance videos. You showed only matrix visual what if we have many visuals in a page and using different functionalities. Please make a video on this

its good thankyou

Life saver

Sumx trick for distinctcount is interesting. I assume replacing All(Calendar) with All(Calender[Date]) inside filter also improves performance in this example?

Big thanks for a great video! I am trying to debug a reeeeally slow Power BI report but got stuck when trying to copy the DAX Query from the performance analyzer. We're using Direct Query in our reports and in my script there's a line for "// SQL Query" and the output says that I can't run the select statement. Is there a way around this?

Great video from @GuyInACube feat @PhilSeamark on how to bake fast DAX. If you're in Wellington, NZ then meet Phil in person on the last Tues of EVERY MONTH at the Wellington Excel & PowerBI User Group. Know PowerBI users in Wellington? Share this so they can find us and geek out!

Great video! Since I found the dax studio plus performance analyzer/VertiPaq, it really spice things up But THE question. What about the “OTHERS” measure listed by the performance analyzer? Hahaha

Excellent article! What tools apart from Dax Studio you all recommend to troubleshooting performance issues? Thanks and have a great week ahead!

What the resources for learning about which functions are time-eaters, and what we can use as alternatives?

Great vid! For some reason though, the measure redefinition in DAX Studio does not work for me. Even when I comment out all of Measure's lines and just make it [Measure] = 1, for some reason when I press Run, it still uses old definition. What am I doing wrong?

Does CALCULATE and FILTER combination has any impact on measure's speed and efficiency? Im struggling to find a solution to slow down my PBI report. Have tried everything on the internet. Thanks in advance, to you guys!

what are the tools other than DAX Studio we can perform this DAX analysis???

Nice tricks, guys. But , what should I do when option Other is the major impact on the visual using performance analyzer ?

You could also try COUNTROWS ( VALUES ( Sales[Customerkey] ) ) in stead of SUMX ( VALUES ( Sales[Customerkey] ) ,1) Don't know if it's faster though....

What if the majority of the time spent by a query is not in the Storage Engine but in the Formula Engine? While my SE queries are at 76, 97% of the query is spent in the FE and not the SE. Any recommendations for troubleshooting FE?

Distinctcount - the scourge..)

Love the tips, SE analogy, and seeing these variables, I’ll def try TREATAS as filter variables. That SUMX was interesting for sure.. I’m just getting into having to optimize my measures in matrix.. would love more tips and options for measures like this.

hmmm ... probably more SUMX than DISTINCTCOUNT in my reports this time :)

Awesome, I am facing with my Power Bi dataset where we have the Import Data instead of Live connection. Will this help approach help me debug the issue? as I just have the dataset and it takes a lot time approximately ~2 hours for refreshing data.

How do I get the new query from dax studio and replace it ln my visual ?

RIP! I have a query that takes 14.55 minutes, (data from1997) is it possible to exclude a query from a data refresh, so that i can create a new truncated (faster) query and than merge the tables to have whole dataset?

I have like 1.8 seconds total only, but the SE Queries are about 400+ ... Does that matter as long as total is not taking too long?

Can you share the file that you're working with for our best practices

Hello, I am I’m experiencing a performance issue with one of my reports and when I run the Performance Analyzer it says that for the time for all actions is slowed down by the “Other” Category. Analyzer report listed below: DAX Query 28 Visual Display 43 Other 16820 I went through the steps that you show in this video and I only have about 1 or two SE Queries according to DAX Studio. Can you point toward where the issue is and how I can further troubleshoot it? Thanks!!

Hello, question about date tables. How to use dates with my country name mounths? Is it posibile?

Hi.... Please if you can make a vedio..in your report add buttons to each visual with and give command to print, or download the visual... Please

I think this approach has some issues. When you use VALUES you also have to consider the fact that one blank row value may be returned, thus the sumx function would have a +1 extra row count.

My DAX studio is showing 39 ms while my performance analyzer is showing 305. Why is that?

In this case, the DAX was taking a lot of time, but what happens when we have visuals where DAX is not the major time consumer? How can you make it faster?

I've seen so few things more horrid than the "yooo" at the start of the video.

Power BI sucks. I would ove an analytical platform where I could build a data model and use pandas to do data manipulation and aggregation.