Extreme DAX

Spis treści książki

• Preface
  • Who this book is for
  • What this book covers
  • To get the most out of this book
    • Get in touch
  • Introduction
  • Part I
  • DAX in Business Intelligence
    • 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
  • Model Design
    • Columnar data storage
      • Relational databases
      • Columnar databases
    • Data types and encoding
    • Relationships
      • Data in Excel
      • Data in relational databases
      • Power BIs 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
  • Using DAX
    • 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
  • Context and Filtering
    • 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
  • Business cases
  • Part II
  • Security with DAX
    • 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
  • Dynamically Changing Visualizations
    • 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
  • Alternative Calendars
    • 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
  • Working with AutoExist
    • 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
  • Intercompany Business
    • 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
  • Exploring the Future: Forecasting and Future Values
    • 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
  • Inventory Analysis
    • 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
  • Personnel Planning
    • 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

pokaż cały spis treści

ukryj recenzje