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.