SQL Server 2012 - Generate, Filter and Sumx DAX Functions in BISM

DAX Query for BISM Tabular Model Part 2

Hello!

We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.

This post comes as a supplementary addition to the earlier post which describes the DAX Query for BISM

Let’s write some more DAX queries.

Please note that I am using the AdventureWorksDWDenali as my database.

And the outcome of the query would be

Now, let’s tweak this query.

1. I want the data for Sales Territory “North America” only.
2. I want more detailed result in terms of products, categories or subcategories.

 Before that, let us first understand what the above stated query

As in BOL,

GENERATE function in DAX:

Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1.

Here, we are performing a Cartesian join between Table 1 which is Sales Territory and Table 2 which is summarized data of measures.

SUMX function in DAX:

This is can be considered as a special form of SUM function in DAX where the SUM is being calculated not over a column specified but for an expression.  

Now back to the tweaking task.

Filter Sales Territory Region

Solution: here just replace the first argument (summarized table) by a filtered summarized table. See the graphic attached below. Please note, in order to avoid repetition, I am just providing the altered query graphic.

DAX query to get detailed result

And the query output would be

Note, a further drill down is also possible to the level of Product Name or to any level, that I would leave to your keenness to explore more data at greater granularity.   

That’s all. Happy Learning!

If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks