Home > PowerPivot > Avoid using FILTER on entire table in PowerPivot DAX

Avoid using FILTER on entire table in PowerPivot DAX

 

Kasper de Jonge has a nice blog item (http://www.powerpivotblog.nl/tune-your-powerpivot-dax-query-dont-use-the-entire-table-in-a-filter-and-replace-sumx-if-possible) showing that using FILTER function on an entite table has performance issue. As FILTER will create a copy of the entire table in the memory, it is a very expensive function in DAX.

Using VALUEs(<Column>) if you are going to filter based on an indivudual column. Rather than FILTER, VALUES creates a one-column table which doesn’t contain duplicated value (think it as Distinct in T-SQL).

Advertisements
Categories: PowerPivot
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: