cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qasqlbi
Regular Visitor

DAX performance killers - what is the community's experience?

I work on models that can take from seconds to 5 minutes+ to render from SQL/SSAS but as I will eventually be using a bespoke web interface, I need the queries to be super quick . I keep the visualisations relatively simple.

 

Now that I am getting into the perforance analyzer / dax studio and power bi aggregations aside, I was just wondering, from your experience, if you have a list of DAX keywords to look out for as you ended up changing/ avoiding them as they are known or likely to be performance killers.

e.g FILTER can be slow.

4 REPLIES 4
mistermat
Frequent Visitor

One other performance improvement I found helpful was to reduce the number of measures a call would have to go through.  Although it's helpful to break down logic into multiple measures, there is a performance hit to doing this way since each measure is itself a CALCULATE function.

Anonymous
Not applicable

I would also warn you against writing DAX where you filter by whole tables instead of individual columns. Such code will almost certainly be slow, at least much slower than properly written code with filters on individual columns.

Best
Darek
Anonymous
Not applicable

Hi there. It's not that FILTER can be, or is, slow or anything like that. FILTER can be super quick and CALCULATE can be super slow. What counts is >your model<. If the model is correct, DAX will be super simple and super quick. If your model is crap, well, calculations will be crap as well whatever technique you use.

Please remember that the best model is the STAR SCHEMA. You also have to pay attention to the granularities of your fact tables. Your columns should have a small number of unique values. Your data types should be correctly and carefully selected. This all is about compression. If the compression ratio is great, your queries will perform greatly. And vice versa.

DAX can be tuned, that's true, but no amount of tuning will make it fast if the model is wrong.

Best
Darek
Anonymous
Not applicable

@Anonymous  is 100% correct. If things are slow chances are your data model is not set up correctly for DAX and/or you are writing your DAX measures to be slow (i.e. Using Filter with a Fact Table). When in doubt try to cut down on the cardinality of the columns and keep tables narrow as possible. Everything works better with longer narrow tables vs. wider shorter tables. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors