Preface

In this book, you'll take your Power BI and Microsoft data analytics skills to the next level. You'll discover the true power of DAX and learn how to build advanced DAX solutions for practical business scenarios.

Who this book is for

If you are an analyst with a working knowledge of DAX in Power BI or other Microsoft analytics tools, this book will help you upgrade your DAX knowledge and work with analytical models more effectively.

This book is not for beginners and practical experience with DAX is necessary.

What this book covers

Chapter 1.1, DAX in Business Intelligence, discusses the field of business intelligence and the central role of analytical models in modern BI solutions. Power BI models are ideally suited for use as such models, not least because of the power of DAX.

Chapter 1.2, Model Design, discusses the foundational concepts of the Power BI model. You learn what makes a Power BI model fundamentally different from other data management products and what an optimal design looks like.

Chapter 1.3, Using DAX, summarizes the different uses of DAX in Power BI models: calculated columns, calculated tables, measures, security rules, and queries. We also give you some best practices for working with DAX.

Chapter 1.4, Context and Filtering, covers row context, query context, and filter context, and the role contexts play in the evaluation of DAX formulas. We discuss how contexts can be transformed using the CALCULATE function, by removing filters and adding filters to an existing context. In addition, we look at time intelligence functions, DAX table functions, the deep connection between tables and filters, and DAX variables.

All of these are foundational concepts in exploring more advanced analyses with DAX. After this important chapter, Part 2 of this book is focused on applying all the concepts discussed so far to real-life business cases, many of them based on the projects we've worked on across the years.

Chapter 2.1, Security with DAX, demonstrates many aspects of securing Power BI models and the power of DAX for doing so. We discuss the versatility of row-level security, security roles, and securing hierarchies, attributes, and aggregation levels through combinations of modeling, DAX, and row-level security.

Chapter 2.2, Dynamically Changing Visualizations, covers how to use helper tables and the SWITCH function to capture user input. We demonstrate how to dynamically change data binding with DAX to create highly dynamic visuals. Depending on your intended use, a helper table can be as simple as a few rows with options, or a larger list based on other data in the Power BI model.

Chapter 2.3, Alternative Calendars, shows you how to implement time intelligence when your calendar looks different than the standard Gregorian calendar that a Power BI model assumes. We close the chapter off with an alternative to relative date filters in Power BI reports, which is more flexible and can handle selections in non-standard calendars as well.

Chapter 2.4, Working with AutoExist, focuses on which calculations are done to populate a visual from a Power BI model. Understanding how AutoExist works will help you to find out why you sometimes do not see results you are expecting in a visual. It also helps to avoid performance problems in reports that are the result of using too many columns from too many tables in one visual.

Chapter 2.5, Intercompany Business, discusses two main business challenges: intercompany business and consolidated views, and invoices to be sent on open sales orders. We discuss how to keep thorough track of context, how to tailor DAX measures to visualizations, and strategies for approaching advanced analyses.

Chapter 2.6, Exploring the Future: Forecasting and Future Values, teaches you about financial metrics for analyzing the future of investments. We discuss the common metrics of Future Value, Present Value, Net Present Value, and Internal Rate of Return, and their equivalents in DAX, including XNPV and XIRR. We also introduce what-if parameters and see how to use them in complex calculations.

Chapter 2.7, Inventory Analysis, deals with analyzing inventory data, although the kind of analysis in this chapter can be applied to all sorts of status-oriented data. We discuss different ways to model this kind of data, how to calculate inventory status at some point in time, and how to compare actuals with targets. You will also see different ways to look into the future, including a linear regression in DAX.

Chapter 2.8, Personnel Planning, discusses ways to analyze the need for personnel (in terms of full-time equivalents, or FTEs) when undertaking projects. From a technical perspective, you will learn ways to work with multiple fact tables that must be considered in combination to provide useful results. The challenge is not only to come up with correct results, but to find the optimal way to compute those results as well.

To get the most out of this book

As mentioned, in this book we assume that you already have practical experience with DAX and are looking to extend your knowledge, applying DAX to a range of more complex scenarios. For this reason, we do not generally explain the way a DAX function and its arguments are used, unless we need to for a specific case; this information can easily be found on the internet.

All models can be found on the book's GitHub site at https://github.com/PacktPublishing/Extreme-DAX. Each chapter contains a link to the PBIX file(s) used in the chapter. We recommend that you use the PBIX files to help you follow along with the chapters.

You can use the latest version of Power BI Desktop to open the PBIX files. When relevant, each figure in a chapter, representing a visualization and the code used, can be found in the PBIX file.

Download the example code files

The code bundle for the book is hosted on GitHub at https://github.com/PacktPublishing/Extreme-DAX. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://static.packt-cdn.com/downloads/9781801078511_ColorImages.pdf.

Conventions used

There are a number of text conventions used throughout this book.

CodeInText: Indicates DAX expressions in the text. For example: "The inactive relationship will save us a lot of ALL('Calendar') clauses in our DAX code."

A block of code is set as follows:

Total Sales = 
CALCULATE(
    SUM(fProjectSales[Budget]),
    USERELATIONSHIP(fProjectSales[StartDate], 'Calendar'[Date])
)

When we wish to draw your attention to a particular part of the text, the relevant lines or items are set in italics. For instance: "Note that we made the relationship between fProjectSales and Calendar inactive. This is done because most calculations will probably not group projects by their start date, but spread results over a period of time beginning with the start date of a project."

Warnings or important notes appear like this.

Tips and tricks appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: Email [e-mail], and mention the book's title in the subject of your message. If you have questions about any aspect of this book, please email us at [e-mail].

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [e-mail] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit http://authors.packtpub.com.

Share Your Thoughts

Once you've read Extreme DAX, we'd love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we're delivering excellent quality content.