Content

Module 1: Introduction to DAX

• What is DAX?
• DAX data types
• Calculated columns
• Measures
• Aggregation functions
• Counting values
• Conditional functions
• Handling errors
• Using variables
• Mathematical functions
• Relational functions

Module 2: Table Functions

• Introduction to table functions
• Filtering a table
• Ignoring filters
• Mixing filters
• DISTINCT Function
• How many values for a column?
• ALLSELECTED function
• RELATEDTABLE function
• Tables and relationships
• Tables with one row and one column
• Table variables

Module 3: Evaluation Contexts

• Introduction to evaluation contexts
• Filter context
• Row context
• Context errors
• Filtering a table
• Using RELATED in a row context
• Ranking by price
• Evaluation contexts and relationships
• Filters and relationships

Module 4: CALCULATE Function

• Introduction to CALCULATE function
• CALCULATE function examples
• CALCULATE function recap
• What is a filter context?
• KEEPFILTERS function
• CALCULATE operators
• Use one column only in a compact syntax
• Variables and evaluation contexts

Module 5: Iterators

• Working with iterators
• MINX and MAXX functions
• Useful iterators
• RANKX function ISINSCOPE function

Module 6: Time Intelligence in DAX

• What is time intelligence?
• Time intelligence functions
• DATEADD function
• DATESINPERIOD function
• Running total
• Mixing time intelligence functions
• Semi-additive measures
• Calculation over weeks

Module 7: Hierarchies in DAX

• What are hierarchies?
• FILTER and CROSSFILTER function
• Percentages over hierarchies
• Parent-child hierarchies

Module 8: ALLSELECTED and Shadow Filter Contexts

• ALLSELECTED function revisited
• Shadow filter contexts

Module 9: Segmentation

• Static segmentation
• Circular dependency in calculated tables
• Dynamic segmentation

Module 10: Many-to-many Relationships

• How to handle many-to-many relationships
• Bidirectional filtering
• Expanded table filtering
• Comparison of the different techniques

LAB Exercises:

Lab 01

• First steps with DAX
• Average sales per customer
• Average delivery time
• Last update of customer
• Working days
• Discount categories

Lab 02

• Percentage of sales
• Delivery working days
• Sales of products in the first week
• Customers with children

Lab 03

• Nested iterators
• Customers in North America (BASIC)
• Create a parameter table

Lab 04 Sales of red and blue products

• Understanding CALCULATE
• Sales of blue products
• Customers in North America (ADVANCED)
• Computing percentages

Lab 05

• Correct sales of grey products
• Best customers
• Customers buying many products
• Large sales
• Percentage of customers
• Counting spikes

Lab 06

• Ranking customers (static)
• Ranking customers (dynamic)
• Date with the highest sales
• Moving average

Lab 07

• Running total
• Comparison YOY%
• Sales in the first three months
• Semi-additive calculations

Lab 08

• Distinct count of countries Sales quantity greater than two

Lab 09

• Static segmentation

Lab 10

• Many-to-many relationships

Module 11: Introduction to Data Modeling

• What is Data Modeling?
• Analytical limits
• Increasing the analytical power
• Introducing the facets of data modeling
• Leveraging the data model
• Normalization and denormalization
• Facts and dimensions
• Introducing star schemas
• Chains of relationships
• How many dimensions?
• Why data modeling is useful?
• Lab 01 – Basic data modeling

Module 12: Header / Detail Tables

• Introduction to header / detail schemas
• Bidirectional filtering is not the way!
• Denormalizing the discount
• Back to star schema
• Lab 02 – Header / detail

Module 13: Working with Date and Time

• Date attributes in the fact table
• Building a date dimension
• CALENDARAUTO function
• The model with a date dimension
• Automatic date grouping in Power BI
• Quick calculations in Power BI Desktop
• Disable automatic date columns
• Handling multiple dates
• Multiple date tables
• Multiple date tables with multiple fact tables
• Multiple relationships with date
• Handling date and time
• Computing with working days
• Create a holidays table (one country)
• Create a holidays table (multiple countries)
• Weekends are not always the same!
• Handling special periods of the year
• Non-overlapping periods in the model
• Non-overlapping special periods
• Overlapping period measure
• Lab 03 – Working with date and time

Module 14: Using Snapshot Tables

• What is a snapshot table?
• Sales versus inventory in the same model
• Non-additive measures
• LASTDATE function does not work here!
• Optimizing performance
• Snapshots and granularity
• Transition matrix
• Parameter tables
• Lab 04 – Snapshots

Module 15: Analyzing Date and Time Intervals What are intervals? Solving with DAX… too complex! Changing granularity Split hours AND amount! Analyzing active events Open orders: the starting model Open orders with DAX Open orders in a snapshot table Events with different durations Daily salary in DAX Precompute the values Lab 05 – Date and time interval

Module 16: Many-to-Many Relationships

• What are many-to-many relationships?
• Possible solutions to this complex scenario
• Bidirectional filtering
• Using CROSSFILTER function
• Using expanded table filtering
• CROSSFILTER versus expanded tables
• Understanding non-additivity
• Cascading many-to-many
• Lab 06 – Many-to-many relationships

Module 17: Working with Different Granularities

• Dimensions define granularity
• Analyzing budget data
• Reduce granularity on all the tables
• Using DAX to move the filters
• Filtering through relationships
• Using the correct column to slice Leveraging relationships
• Checking granularity in the report
• Hiding or reallocating?
• Lab 07 – Granularity