Common Dax Expressions and Power
BI Playbook
A STRUCTURED APPROACH TO POWER BI DESIGN
Andrew McSwiggan
BUSINESS INTELLIGENCE SPECIALIST
www.pluralsite.com
Version: 2.93.641.0 64-bit
(May 2021)
Create DAX expressions
Best functions
Single functions
Summary Combining functions
Collections
Columns
Measures
Structure
Tables
Relationships
Common Dax Expressions
Module 02
A Structured Approach to Power BI Design
Course Progress
20%
Common Dax Expressions
Module 03
Creating Text Columns for Data Analysis
Course Progress
30%
Common Dax Expressions
Module 04
Creating Calendar Columns for Data Analysis
Course Progress
40%
Common Dax Expressions
Module 05
Creating Measures with Aggregation Functions
Course Progress
50%
Common Dax Expressions
Module 06
Creating Groups Bands and Hierarchies with Conditional Values
Course Progress
60%
Common Dax Expressions
Module 07
Creating Intelligent Measures with Iterating X Functions
Course Progress
70%
Common Dax Expressions
Module 08
Taking Control of the Evaluation Context
Course Progress
80%
Common Dax Expressions
Module 09
Filtering Measures with Time Intelligence Functions
Course Progress
90%
Common Dax Expressions
Module 10
Creating Complex Measures for RFM Analysis
Course Progress
100%
Building a Foundation
Dimensional Data Model
Data modeling is easy.
Dimensional modeling.
Dimensional Data Model
Base Data
What is a Dimensional Model?
Dimensional Model
Dimensional modelling (DM) is part of the Business Dimensional
Lifecycle methodology developed by Ralph Kimball which
includes a set of methods, techniques and concepts for use
in data warehouse design
Source Wikipedia
ERD
Entity relationship Data map Database
diagram
What is a Dimensional Model
Entity relationship diagram (ERD)
A series of connected data tables
Represented as a data star (star schema)
This structure includes
- One fact table (Metrics)
- Many dimension tables
- The fact joins to all dimension tables
New Model View
What does a Dimensional Model do?
Why use a Dimensional Model
Better Power BI Basic building blocks Organize attributes
models
Joining Dimensions and Facts
Web
Product
Dimension
Table 123
Product
Code
Mobile
123
123
Mobile
123
The Dimensional Modeling Process
Transformation
Data Source
Star Schema
Data Source
Dimension Tables
Dimension Tables
Measures
The Dimensional Modeling Process
Grain Row in the fact table Key combination
Star Schema
Fact Table Rows
Exploring the data
What is a pbix file
The data The code Visual worksheets
Column Lists
Column Lists
Designing the Data Model
Designing the Data Model
Define the dimension Identify the grain Define the fact table
tables
Measures
Sales quantity Sales value Record measures
Creating Dimension Tables with GROUPBY
Column and Table Functions
Functions Columns Tables
DAX Function
Easy to use
GROUPBY Identifies Unique combinations of columns
Use this
- Create Dimension tables
Base Data
GROUPBY (Table, Column , Name , Expression)
GROUPBY
Table Primary source table
Column List List of columns
Name , Expression Named Aggregation
GROUPBY (Table, Column List, Name, Expression… )
GROUPBY(
DAX_Training_Sales_Transactions ,
Product Code , Product Description , Unit Price )
Creating a Fact Table with
SUMMARIZECOLUMNS
DAX Function
Similar to GROUPBY
Permits CALCULATE function
SUMMARIZECOLUMNS
Permits FILTER function
Use this to
Create Fact tables
SUMMARIZECOLUMNS(
GROUPBY column list ,
Filter list ,
Name , Expression list )
SUMMARIZECOLUMNS
GROUPBY column list Column Names to GROUP BY
Filter list Filters to Apply to the output table
Name , Expression list Name and create measure columns
Creating a Data Star with the Model tool
Dimensional modeling
Star schema’s
Summary Explore your source data
Design
- Dimension Tables
- Fact Tables
Model
- Data Star
- Validate relationships
