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.
- I want the data for Sales Territory “North America” only.
- 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!
Regards
Raunak Jhawar
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
