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.

@samanateeq3203

3 жыл бұрын

How about Countrows( Values (Column name) ) ,why Sumx @Guy in a Cube

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..

@GuyInACube

4 жыл бұрын

Phil is a great person and super smart! We were happy to get him onto the video. 👊

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

@GuyInACube

4 жыл бұрын

Yeah it was pretty neat. Just be careful as it won't perform better every time. Test and verify ;) 👊

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

@GuyInACube

4 жыл бұрын

Appreciate that! We love the troubleshooting stuff. Thanks for watching. 👊

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

@GuyInACube

4 жыл бұрын

Love it! Thanks for watching 👊

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.

@GuyInACube

4 жыл бұрын

YES!!! It's the little things that help you move the needle 👊

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

@GuyInACube

4 жыл бұрын

Awesome! We definitely want to get more content out like this. Thanks for watching! 👊

Awesome. Thanks Adam and Phil 👍👍

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

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

Awesome! Love this. Thank you team.

@GuyInACube

4 жыл бұрын

Most welcome! Thanks for watching. 👊

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.

Thank you for all of your work

@GuyInACube

4 жыл бұрын

Thanks

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

@GuyInACube

4 жыл бұрын

Awesome! Glad to hear this may help you track some things down. Thanks for watching. 👊

Perfect, thank you!

very handy lads, thanks

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

@MrRJolly

4 жыл бұрын

Please remember, this won't always be faster. Just try it with your dataset and see.

@GuyInACube

4 жыл бұрын

Agreed with Phil. Your mileage may vary. Always test and verify based on your dataset and query patterns. The distinct count part wasn't the point of the video and was just a quick example Phil threw together to talk about the debug technique. Talking to Marco Russo, he suggested, for DISTINCTCOUNT specifically, to try to understand why DISTINCTCOUNT is performing badly and try to fix that first before replacing it.

@kyleparsons1735

4 жыл бұрын

@@GuyInACube Right right right, that makes sense. Thanks for the clarification guys!

Great video 👌💯

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

@GuyInACube

4 жыл бұрын

Love it! It has helped us numerous times 👊

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.

Thanks for this super video. Trying it out now

@GuyInACube

4 жыл бұрын

Most welcome! Thanks for watching. 👊

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

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.

@GuyInACube

4 жыл бұрын

The grocery store analogy was great! Steal away 😀 Yeah results will always vary. Everyone's data is different.

Useful technique, thx a lot

@GuyInACube

4 жыл бұрын

Most welcome. Thanks for watching! 👊

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

@MrRJolly

4 жыл бұрын

Woah! Nice, was that just from using this technique?

@GuyInACube

4 жыл бұрын

WOW!!! That's amazing. Did you achieve that by using this technique?

@vog5197

4 жыл бұрын

@@GuyInACube Yeah! Phil and your enthusiasm made me start doubting myself, so I replicated the whole experiment, here are the two DAX studio results: (not sure if youtube allows links) abload.de/img/daxstudio0wjgp.png

@MrRJolly

4 жыл бұрын

@@vog5197 Worked for me. Very impressive and well done! :)

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

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?

@MrRJolly

4 жыл бұрын

Not by much. Clearing filters is fast. However if I was adding filters, I would target specific columns rather than an entire table.

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

@GuyInACube

4 жыл бұрын

Thanks Chip! This was a really fun one to do. Love digging in like this as it pushes us to really learn how things work.

Love the Bifocal shirt as well :D

@GuyInACube

4 жыл бұрын

Love the BIFocal guys (Jason and John). Listen to the podcast if you can. 👊

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!

@GuyInACube

4 жыл бұрын

Appreciate that Jeffrey. Definitely a treat to sit down with Phil. Phil is amazingly smart.

its good thankyou

@GuyInACube

4 жыл бұрын

Glad you liked it Ali! 👊

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 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?

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

@MrRJolly

4 жыл бұрын

Often that reflects how much time visuals are spent queuing behind one another to render. Might be a good topic for another video.

@UTUBE787878

4 жыл бұрын

@@MrRJolly Hi Phil, great video , very interesting. But I have a report with a lot of measures, each one only takes few ms for DAX query but more than 1000 ms in 'other' !! Where does it come from this 'other' time ? How can we debug this ? A video on that topic would be very helpful !! Thanks in advance, Pierrick

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.

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 the resources for learning about which functions are time-eaters, and what we can use as alternatives?

@GuyInACube

4 жыл бұрын

As with all things, it depends. a given function may perform well in one situation, but then not with another. I would highly recommend getting the 2nd edition of the Definitive Guide to DAX. It is really helpful in understanding what is happening, especially under the hoods. Be sure to test and validate your items to also understand more.

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

@GuyInACube

4 жыл бұрын

VertiPaq Analyzer from sqlbi.com is a great tool that we use quite frequently to inspect the model. SQL Profiler can also be used for tracing, but DAX Studio does that as well and I can do more with that. Those are the main tools we use.

@luisedugarridosoares

4 жыл бұрын

Guy in a Cube Thank you all! Have a great week!

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!

Distinctcount - the scourge..)

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

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

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?

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

@MrRJolly

4 жыл бұрын

How many visuals do you have on your page? Might be some queuing going on

@carlosmarconybribeiro5748

4 жыл бұрын

More than 30, including cards, tables and background images.

@GuyInACube

4 жыл бұрын

yeah, that is probably one of the major contributors in your case. You should look at reducing the number of visuals. You mention tables as well. I've seen where tables and matrix visuals can contribute to it especially if you have a lot of stuff going into them.

@carlosmarconybribeiro5748

4 жыл бұрын

@@GuyInACube Thanks. I'll try. Congratulations for the channel. It's helping a lot to create amazing dashboards here. Come visit Brazil.👍

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?

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.

@MrRJolly

4 жыл бұрын

This technique will help you work on slow running DAX queries (import or DQ). It will not help you diagnose refreshing.

@MrPranavpatel72

4 жыл бұрын

@@MrRJolly Thanks for confirmation!! If you can just suggest what method can be used to see where exactly it take long for refreshing?

@GuyInACube

4 жыл бұрын

It depends on the data sources. You do have some logging you can enable in Power BI Desktop for the mashup engine. This may help you but is hard to read. If it is SQL Server, i'd recommend getting tracing information from the SQL side as part of your data collection. It is not really straight forward though unfortunately.

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

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

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?

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

@GuyInACube

4 жыл бұрын

Thanks for the video Sahar. If you'd like to provide product feedback to the Power BI Product team, you can give that at ideas.powerbi.com.

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?

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!!

@GuyInACube

3 жыл бұрын

If the "Other" bucket is the only large item you see, chances are you have too many visuals on your report. Check out this video for an example: kzread.info/dash/bejne/nZ99utavhZSnmbA.html

@theanti-winesnobclub2276

3 жыл бұрын

Guy in a Cube Thank you for your timely response! It turned out that it was the OKVIz Smart Filter that was making the report very slow. I removed it and added the regular slicers and now the report is flaying!! Has this happened to anyone else?

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?

@GuyInACube

4 жыл бұрын

There is only so much you can do. Typically, the only major thing you could do is either limit the number of visuals, or if it is a table/matrix, try to make it no so complex so it doesn't have to spend as much time from a rendering perspective. Custom visuals also tend to have a higher render overhead than the base visuals.

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.

@skywalknotpossible

Жыл бұрын

and that's exactly what it is not. it's not an analytical tool.