1 option
Extreme DAX : Take your Power BI and Fabric analytics skills to the next level / Michiel Rozema, Madzy Stikkelorum, Henk Vlootman.
- Format:
- Book
- Author/Creator:
- Rozema, Michiel.
- Language:
- English
- Subjects (All):
- Business intelligence.
- Predictive analytics.
- Physical Description:
- 1 online resource (554 pages)
- Edition:
- 1st ed.
- Place of Publication:
- Birmingham : Packt Publishing, Limited, 2026.
- Summary:
- Master advanced DAX in Power BI and Microsoft Fabric to build scalable semantic models, solve complex business problems, and deliver high-impact analytics using real-world scenarios and proven techniques.
- Contents:
- Intro
- Extreme DAX
- Second Edition
- Take your Power BI and Fabric analytics skills to the next level
- Foreword
- Contributors
- About the authors
- About the reviewers
- Table of Contents
- Preface
- Who this book is for
- What this book covers
- To get the most out of this book
- Download the example code files
- Conventions used
- Get in touch
- Share your thoughts
- Free benefits with your book
- How to unlock
- Join our community on Discord
- 1
- Analyzing Data with DAX
- The five-layer model for business intelligence
- Enterprise BI and end-user BI
- Fabric and Power BI
- Where DAX fits in, and where to find it
- Excel
- Power BI and Fabric
- SQL Server Analysis Services
- Azure Analysis Services
- Tools to develop semantic models and DAX
- Powered by DAX: visual, interactive reports
- The data-driven transformation cycle
- How to approach solution development
- Using semantic models for BI solution development
- We do not know exactly what we need
- Our data is not correct
- Summary
- Get this book's PDF version and more
- 2
- Model Design
- 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
- Limited relationships
- 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
- Architectural options in semantic models
- Import models
- DirectQuery
- DirectLake
- Composite models.
- Import and DirectQuery
- DirectQuery from different sources
- DirectLake and import
- DirectQuery to semantic models
- Semantic link
- 3
- Using DAX
- Technical requirements
- Calculated columns
- Calculated tables
- Measures
- Visual calculations
- DAX security filters
- Field parameters
- Calculation groups
- DAX queries
- User-defined functions
- 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
- 4
- 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
- 5
- Security with DAX
- Introduction to row-level security (RLS)
- Security roles
- Security filters and relationships
- Dynamic RLS
- Modeling considerations for RLS
- Testing security roles
- 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
- VLS: modeling
- VLS: security filters
- VLS: advanced scenarios
- How to develop in models with value-level security
- Dealing with multi-role membership
- Securing aggregation levels
- Measures cannot be secured, but fact tables can
- Restricting fact table granularity
- Securing aggregation levels with composite models
- Combining aggregation security with VLS
- Securing an aggregation level as an attribute
- 6
- Dynamically Changing Visualizations
- The business case
- Dynamic measures
- The basic KPI measures
- Creating a field parameter
- Creating a visual with dynamic measures
- Excluding certain measure combinations
- Dynamic labels
- Creating a visual with dynamic measures and dynamic axes
- Dynamic report titles
- Excluding combinations of specific measures and labels
- Dynamic date selection
- 7
- Inventory Analysis
- Data modeling for status-oriented data
- Inventory granularity
- The business case and model
- Basic inventory calculations
- Inventory targets
- Inventory forecasting
- Two types of forecasts
- 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
- 8
- 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 year and week columns
- Creating additional columns
- Defining a custom calendar
- Time intelligence calculations for week-based calendars
- Calculating cumulative results
- Calculating sales growth
- Moving average by week
- Organize time intelligence with calculation groups
- Excluding measures from calculation groups
- Drill-through interaction with calculation groups
- Calculation formats on drill-through pages
- Keeping your report current
- The date selection table
- Creating selection options
- Applying date selection
- 9
- Working with Auto-Exist
- Introducing 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 Auto-Exist is, and what it does
- Using multiple filters in a visual
- How Auto-Exist optimizes DAX evaluation
- Example: The case of the missing workdays
- Model structure
- Sales 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 Auto-Exist
- Always consider the context!
- Fixing the workday calculation
- Optimizing report performance with Auto-Exist
- Granularity in fact tables
- Filtering on multiple fact tables
- Optimizing model structure
- Optimizing the visual
- Using visual calculations to optimize performance
- 10
- Recursion in DAX
- Concerning DAX user-defined functions
- Function parameters
- Context in functions
- Parameter passing modes
- Naming conventions.
- The business case
- Intra-day time intelligence
- Analyzing shipments
- Computing average shipment quantity
- Semi-recursion with DAX user-defined functions
- Compute results for a single hour
- Gather results for all hours
- Average shipment quantity
- Advanced recursion
- Consider the initial state
- Extending the recursion
- Leveraging calculated tables
- 11
- DAX-Driven Waterfalls
- Concerning visual calculations
- A simple example
- Creating a waterfall with visual calculations
- Setting up a simple all-positive waterfall
- Creating visual calculations for a simple waterfall
- Allowing negative values
- Coloring the deltas
- Reusing visual calculations
- Creating a UDF for marking minimum and maximum values
- Creating waterfall UDFs
- Why do we need two hidden helper columns?
- A general function to call
- How to create a new waterfall chart
- Dynamic baseline and final values
- Creating dynamic categories
- 12
- Benchmarking the Neighbors
- Explaining the window functions
- Examples of the window functions
- Creating a dynamic revenue analysis
- Returning revenue of previous rows
- Returning a range of rows
- What else you can do with window functions
- Benchmarking
- Finding the neighbors
- How much sales do the neighbors have?
- Fixing the totals
- Finding different neighbors
- Making the number of neighbors dynamic
- 13
- Real-Estate Investment Planning
- Financial calculations
- Present value and net present value
- Internal rate of return
- Financial DAX functions
- Creating adjustable rates and indexes
- Calculating future values
- Initial investment and residual value
- Irregular cash flows
- Recurring cash flows.
- Total future value.
- Notes:
- Description based on publisher supplied metadata and other sources.
- Part of the metadata in this record was created by AI, based on the text of the resource.
- ISBN:
- 1-83664-762-X
- OCLC:
- 1590084337
The Penn Libraries is committed to describing library materials using current, accurate, and responsible language. If you discover outdated or inaccurate language, please fill out this feedback form to report it and suggest alternative language.