Extreme DAX: Take your Power BI and Microsoft data analytics skills to the next level
- Length: 470 pages
- Edition: 1
- Language: English
- Publisher: Packt Publishing
- Publication Date: 2022-01-20
- ISBN-10: 1801078513
- ISBN-13: 9781801078511
- Sales Rank: #465379 (See Top 100 Books)
Discover the true power of DAX and build advanced DAX solutions for practical business scenarios
Key Features
- Solve complex business problems within Microsoft BI tools including Power BI, SQL Server, and Excel
- Develop a conceptual understanding of critical business data modeling principles
- Learn the subtleties of Power BI data visualizations, evaluation context, context transition, and filtering
Book Description
If you want to get the most out of Microsoft Business Intelligence tools, sooner or later you’ll need to master DAX. Extreme DAX enables business analysts to generate powerful and sophisticated analyses from their data.
You’ll learn the principles of business intelligence, good model design, and how DAX fits into it all. Then, you’ll launch into detailed examples of DAX in real-world business scenarios such as inventory calculations, forecasting, intercompany business, and data security. At each step, senior DAX experts will walk you through the subtleties involved in working with Power BI models and common mistakes to look out for as you build advanced data aggregations.
You’ll deepen your understanding of DAX functions, filters, and measures, and how and when they can be used to derive effective insights. Also provided with the book are PBIX files for each chapter for you to follow along with and explore in your own time.
What you will learn
- Understand data modeling concepts and structures before you start working with DAX
- Grasp how relationships in Power BI models are different from those in RDBMSes
- Secure aggregation levels, attributes, and hierarchies using PATH functions and row-level security
- Get to grips with the crucial concept of context
- Apply advanced context and filtering functions including TREATAS, GENERATE, and SUMMARIZE
- Explore dynamically changing visualizations with helper tables and dynamic labels and axes
- Work with week-based calendars and understand standard time-intelligence
- Evaluate investments intelligently with the XNPV and XIRR financial DAX functions
Who this book is for
If you are an analyst with a working knowledge of DAX in MS 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.
Preface Who this book is for What this book covers To get the most out of this book Get in touch Part I: Introduction The Five-Layer model for business intelligence Enterprise BI and end-user BI Where DAX fits in, and where to find it Excel Power BI SQL Server Analysis Services Azure Analysis Services Tools to develop models and DAX Powered by DAX: visual, interactive reports How to approach solution development Using Power BI models to accelerate BI solution development We do not know exactly what we need Our data is not correct The digital transformation cycle Summary Columnar data storage Relational databases Columnar databases Data types and encoding Relationships Data in Excel Data in relational databases Power BI's relational model Relationship properties Active and inactive relationships Cross filter direction Cardinality Effective model design Star schemas and snowflakes The issue with star schemas RDBMS principles to avoid in Power BI models Interdependent dimensions One fact table only Data warehouse as the single source of truth Using many-to-many relationships Memory and performance considerations Summary Calculated columns Calculated tables Measures DAX security filters DAX queries Date tables Creating a date table Best practices in DAX Think in terms of DAX measures primarily Build explicit measures Use base measures as building blocks Hide model elements Do not mix data and measures – use measure tables instead Table types Summary The Power BI model Introduction to DAX context Row context Query context Filter context Detecting filters Comparing query and filter context to row context DAX filtering: Using CALCULATE Step 1: Setting up a filter context Step 2: Removing existing filters Step 3: Applying new filters Step 4: Evaluating the expression to calculate Removing filters with ALL functions Time intelligence Changing relationship behavior Table functions in DAX Table aggregations Using virtual tables Context in table functions Performance considerations using table functions Filtering with table functions Using CALCULATETABLE Filters and tables Using TREATAS DAX variables Summary Part II: Business cases Introduction to row-level security (RLS) Security roles DAX security filters Dynamic row-level security Modeling considerations for RLS Testing security roles Testing live connection reports Impersonation model Adding the pImpersonation table to the model Adding a test security role Making it all work Securing hierarchies using PATH functions Hierarchical tables Introducing PATH functions PATH PATHCONTAINS PATHLENGTH PATHITEM PATHITEMREVERSE Using PATH functions in RLS Advanced hierarchy navigation in RLS Securing attributes The case for secured attributes Object-level security and its restrictions Dynamically securing attributes: introducing value-level security Value-level security: modeling Value-level security: security filters Value-level security: advanced scenarios How to develop in models with value-level security Securing aggregation levels Measures cannot be secured, fact tables can Restricting fact table granularity Securing aggregation levels with composite models Combining aggregation security with value-level security Securing an aggregation level as an attribute Summary The business case Dynamic measures The basic KPI measures Creating a helper table Creating a dynamic DAX measure Selecting both the calculation and date columns dynamically Dynamic labels Solution overview Creating a helper table Creating a DAX measure using dynamic labels Combining dynamic labels and dynamic calculations Summary Week-based and Gregorian calendars What is a week-based calendar? Week numbers Periods Quarters Years Creating a week-based calendar table Setting up dates Finding the correct start date Finding the correct end date Creating additional columns Time Intelligence calculations for week-based calendars The Power BI model Calculating year-to-date results Calculating sales growth Moving average by week within an accounting year Keeping your report current The Date Selection table Creating selection options Using Date selection in measures Summary The Power BI model How Power BI visualizes the output of a model Visual filters and context How using measures changes the behavior of visuals Understanding a visual's DAX query What AutoExist is, and what it does Using multiple filters in a visual How AutoExist optimizes DAX evaluation Example: The case of the missing workdays The business case Model structure Order intake analysis Extending the Calendar table Workday analysis Where's my workday gone? How to solve the missing workdays problem The root of the problem Changing model structure to get around AutoExist Always consider the context! Fixing the workday calculation Optimizing report performance with AutoExist Granularity in fact tables Filtering on multiple fact tables Optimizing model structure Optimizing the visual Summary Modeling the QuantoBikes sales process The sales process Model structure Business between subsidiaries Subsidiary view versus consolidated view Matching internal sales and purchases Visualizing intercompany business Future sales Sales on one-off sales orders Sales on long-term sales orders Dealing with old sales orders Dealing with current sales orders Optimizing the current sales order calculation Further optimization Testing complex calculations Summary Financial calculations Present Value and Net Present Value Internal Rate of Return Financial DAX functions The business case and model Creating adjustable rates and indexes Calculating Future Value (FV) Initial investment and residual value Irregular cash flows Recurring cash flows Positive and negative cash flows Calculating Net Present Value (NPV) Calculating the Internal Rate of Return (IRR) Calculating cost-covering rent Determining cost-covering rent by approximation Optimizing the approximation Summary Data modeling for status-oriented data Inventory granularity Basic inventory calculations Inventory targets Inventory forecasting Two types of forecast Using a sales forecast to predict inventory changes Using extrapolation to predict inventory changes Calculating long-lasting inventory Working with forecast-based inventory targets Using linear regression for extrapolating inventory Summary The Power BI model Calculating sales Optimizing the sales calculation Calculating FTEs needed Considering totals Optimizing the FTE calculation Optimizing the Power BI model Considering aggregation levels Summary Other Books You May Enjoy Index
Donate to keep this site alive
How to download source code?
1. Go to: https://github.com/PacktPublishing
2. In the Find a repository… box, search the book title: Extreme DAX: Take your Power BI and Microsoft data analytics skills to the next level
, sometime you may not get the results, please search the main title.
3. Click the book title in the search results.
3. Click Code to download.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.