Dax queries : Embedded calculations vs Calculated measures

Question

• Dear community

I am using ssas tabular on a major project and I have noticed something both disturbing and weird.

Basically I have many SSRS reports that read from a Tabular Cube; there I noticed that if I use calculated measures defined in the cube in my queries they become *really really* slow, but if instead; I take exactly the same code as the measure and embed in my dax query it replies instantly.

Now if I connect to the cube via excel, and use that same calculated measure, I do not notice any performance issues...

Does anybody have an idea on what would be the cause for this?

Thanks so much

Monday, July 14, 2014 10:43 AM

All replies

• Hi,

If I understanding correctly, the performance of using embedded calculations is much better than that using calculated measures in query, right?

Tabular models use Data Analysis Expressions (DAX), a formula language for creating custom calculations. Calculations are defined based on a combination of cube data, arithmetic operators, numbers, and functions using DAX. As per my understanding, the embedded calculations were calculated while processing the database. However, the calculated measures in query will calculate the value while executing the query. So the performance is less than using embedded calculations.

If I have anything misunderstanding, please point it out.

Regards,

Charlie Liao
TechNet Community Support

Tuesday, July 15, 2014 9:10 AM
• No that's not correct. It is the other way around, let's say I have a measure in my cube: [# Customers]:= CALCULATE(fancy dax stuff)

I connect with Excel pivot table, I drag and drop [# Customers], then add many dimensions => Works like a charm.
Now I create a DAX query to feed an SSRS report, within this query if:

A) I use the measure [# Customers]: It is slow as hell
B) I copy paste the formula for [# Customers] and use it in my query (instead of referencing it as in (A)) the performance is good...

I do not understand why calculated measures define inside the cube are so slow when queried with a DAX query (but not when queried via excel).

Is that clearer?
Tuesday, July 15, 2014 10:08 AM