Chapter 19

Advanced Topics in DAX

What's in this chapter?

  • Parent-child hierarchies
  • Cross-filtering with many-to-many relationships
  • Role-playing dimensions
  • Time-based averages
  • Non-aggregatable attributes

In Chapter 18 you learned the basic concepts of DAX expressions and queries with several examples, and you became familiar with DAX functions, their categorization, and their purpose. In this chapter you take a list of typical Business Intelligence (BI) problems and solve them with tabular BISM with DAX. You learn how to define parent-child hierarchies using specific DAX functions, and how to analyze the data. Tabular models do not natively support many-to-many relationships, so this chapter shows you how to define many-to-many relationships between tables with DAX. You also learn various options for modeling role-playing dimensions. Finally, you also look at additional Time Intelligence function examples.

In this chapter you consider business scenarios and their implementation in BISM tabular using DAX. These scenarios include problems that do not have native DAX solutions and require special handling. The scenarios also include building parent-child hierarchies and performing aggregations for them, creating DAX calculations using cross filtering and performing time-based analysis using Time Intelligence functions. You walk through the scenarios step by step so that you understand how to define these, and you can design your tabular BISM efficiently for your specific organizational ...

Get Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.