DAX Querying Part I


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 
(  
    ‘dates’ 
)

 

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 
(  
    values(‘dates'[year])
)

 

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

evaluate 
(  
    summarize
    ( 
        ‘dates’
        , ‘dates'[year]
    )
)

 

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
    (
        values(‘dates'[Year])
        , “yearly sales”sum(‘Sales'[amount])
    )

)

 

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

evaluate
(
    addcolumns
    (
        values(‘dates'[Year])
        , “yearly sales”calculate(sum(‘Sales'[amount]))
    )
)

 

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
(
    addcolumns
    (
        values(‘dates'[Year])
        , “yearly sales”calculate(sum(‘Sales'[amount]))
        
        , “store 1 sales”calculate(sum(‘Sales'[amount])
                                    , ‘Stores'[store_name]=“Store 1”
                                    )
    )

)

 

Advertisements

4 thoughts on “DAX Querying Part I

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

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

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

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