SQL Server 2012 BISM – Generate, Filter and Sumx DAX Functions

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.

1_SQL_Server2012_Generate_Filter_Sumx_DAX_Functions_in_BISM

And the outcome of the query would be

2_SQL_Server2012_Generate_Filter_Sumx_DAX_Functions_in_BISM

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.

   

3_SQL_Server2012_Generate_Filter_Sumx_DAX_Functions_in_BISM

DAX query to get detailed result

4_SQL_Server2012_Generate_Filter_Sumx_DAX_Functions_in_BISM

And the query output would be

5_SQL_Server2012_Generate_Filter_Sumx_DAX_Functions_in_BISM

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!

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

Leave a Reply

Your email address will not be published.