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

[qasqlbi]

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.

[mistermat]

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]

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]

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]

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