This post looks the basics for querying tabular models using a DAX query. While Tabular models can be queried with MDX, DAX is the native language for tabular databases and may provide better results than MDX. This post looks how to building a simple table in DAX so that a row set can be exposed.

The basis of record-set queries in DAX is the evaluate statement which returns the table that is defined in the functions argument. The argument can be any statement that forms a table. For example, we can simply pass a table to the statement and return all the rows and columns from it;

evaluate |

More likely though, we will want to define table which is defined by our query, that is, we define the table which will return the results needed. As more and more DAX queries are used and the community develops its standards, it is likely that there will be a query pattern which forms. For now though, consider that the argument of evaluate can be anything that is returned as a table.

Consider selecting distinct years. Since the values function returns a table (of a single column), we can be use the values function as the argument for an evaluate.

evaluate |

Similarly, summarize returns a table and produce the same results.

evaluate |

The key point here is that we need to define a table as part of the evaluate statement.

**Extending the Defined Table with ADDCOLUMNS
**

Usually, we want our *defined* table to combine data from another table. There are a few ways we can go about this and one method is to use the ADDCOLUMNS function. As the name suggests, ADDCOLUMNS adds a calculated column to a table. Note that a new column is defined both its name and expression.

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)

It is interesting to note the evaluation context of the expression. While we may expect a calculation to be evaluated in the context of the row it is being added to, the context applies to the entire model. For example, consider summing the sales amount by year. Extending our *yearly values* table by adding a column to sum sales amount is evaluated in the context of the entire sales table (as below). This is akin to the query context in MDX for measures that are defined with the WITH MEMBER MEASURES.[*Measure Name*].

evaluate addcolumns ) |

In order to change the evaluate context of the calculation to the row, we must use the calculate statement.

evaluate |

Any filters that are applied with the calculation context are evaluated with the calculations context. Therefore (as expected), to retrieve a single store sales, we would include it in the calculate statement.

evaluate ) |

Pingback: DAX II – Extending the use of Evaluate « Paul te Braak

Pingback: Tabular Queries in Excel 2013, Good news, Bad news,DAX Queries and the missing DAX visual query builder « Rui Quintino Blog

Hi,

I tried this procedur, but it seems that it filters the data processed in Excel’s pivotable, but all the data in PowerPivot’s data model, remain unfiltered. Am I corrcete?

I would very much like to know how I could filter the data imported into PowerPivot (from a SQL DB) based on user selected data in a cell in Excel.

Can you help?

Best Regards

Jose Lourenco

Hi Jose,

I think that you may have strayed from the path a little. PowerPivot is just an engine to house data in much the same ways as the relational engine. The query determines what is shown … besides … what do you care what is in the powerpivot model? Of course you can restrict the data going in (ie on the import) but that’s a different story.

If you want to filter another pivot based on what the user has selected in one pivot, you may want to look at this post https://paultebraak.wordpress.com/2013/01/25/pivot-filtering-with-cell-selection-and-vba/ Chris, has also adapted this for table (pivot) results in this post http://cwebbbi.wordpress.com/2013/02/15/dynamic-dax-query-tables-in-excel-2013/