CALCULATETABLE | A blog about Analytics, AWS and Azure

What can we do to make queries as fast as possible? Here is some tips 🙂

In my simple test I want to see the sales for year 2009. To make sure my queries does not hit any cache I’m clearing it using XMLA. In my first query I will try to use the ROW function and sum up the internet sales

EVALUATE
ROW (
"Sales", CALCULATE (
SUM ( 'Internet Sales'[Sales Amount] ),
'Date'[Calendar Year] = 2008
)
)

When watching the profiler I can see that this query took 156 milliseconds. The reason why this takes quite long is that we are one the row level.

Lets switch to table level.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales", SUM ( 'Internet Sales'[Sales Amount] )
),
'Date'[Calendar Year] = 2008
)

This query took 78 milliseconds. Twice as fast as the first query.

What if I move the calculation using ADDCOLUMNS outside the SUMMARIZE?

EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE ( 'Date', 'Date'[Calendar Year] ),
'Date'[Calendar Year] = 2008
),
"Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)

This query took 62 milliseconds

Do you have an even better optimalization tip? Feel free to drop me an mail

In this post I will give you some samples on how to use the “calculatetable” function. In MSDN it is described as “Evaluates a table expression in a context modified by the given filters”. This function will always return all the columns in the table. And you have the possibility to add new columns and add filters.

Lets take a look at some examples:

EVALUATE
CALCULATETABLE ( 'Internet Sales' )

The code above will basically give you all the columns and all the rows in the “Internet Sales” table. This will be equal to a “select * from table” in SQL.

In the next sample I will show you how to filter the resultset in “calculatetable”

EVALUATE
CALCULATETABLE (
    'Internet Sales',
    'Date'[Calendar Year] = 2005,
    'Internet Sales'[ProductKey] = 314
)

As you can see here I have added filters for year 2005 and productkey 314.

A blog about Analytics, AWS and Azure

Tag Archives: CALCULATETABLE

Basic query optimalization in DAX

Evaluate a table expression using “calculatetable”