Microsoft Power BI DAX and Data Modeling

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