Basic data models are simple to construct using Power BI Desktop. However, Data Analysis Expressions are required if you wish to report across time periods—for example, to display year-over-year growth or to compare sales or other data by month or quarter (DAX). DAX is a programming language for creating Power BI calculations that extend your data model.
Learn how to use DAX aggregate functions and add calculated columns, build measurements, and use DAX logical and filter functions and recommended practices for data model construction and optimization.
Whether you're using Power BI Desktop, Power Pivot in Excel, or Analysis Services, learning Data Analysis Expressions (DAX) is essential to creating effective data models.
Training Duration: 5 Days
- Certificate Of Completion Available
- Group Private Class
- VILT Class Available
- HRDCorp SBL-Khas Claimable
This GRC-112: Microsoft Power BI Data Modeling with DAX workshop is a complete course about the DAX language. DAX is the native language of Power BI, Power Pivot for Excel, and SSAS Tabular models in Microsoft SQL Server Analysis Services.
This Power BI training for DAX is aimed at users of Power BI, Power Pivot for Excel, and at Analysis Services developers that want to learn and master the DAX language. This course covers the latest version of DAX 2019.
This workshop is a complete course about building the most optimal data models for your Power BI reports. It introduces the audience to the basic techniques of shaping data models in Power BI. It offers many real-world examples that will help you look at your reports in a different way – pretty much like experienced data modelers do.
After completing this course, students will be able to:
- Understand all the features of the DAX language
- Write formulas for common and advanced scenarios
- Learn the vast techniques of shaping and building a correct data model to be used in Power BI.
- Understand the common challenges and mistakes that people have in creating a data model.
- Learn the most optimum way of building a sustainable data model for Power BI reports
Attendees need to have a basic knowledge of the data modeling in Power Pivot for Excel, or Power BI Desktop, or Analysis Services Tabular 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
GRC-102: Conquering DAX 2019
This GRC-102: Conquering DAX 2019 workshop is a complete course about the DAX language. DAX is the native language of Power BI, Power Pivot for Excel, and SSAS Tabular models in Microsoft SQL Server Analysis Services.
GRC-104: Intensive Data Modeling For Microsoft Power BI Creators
This intensive Power BI data modeling workshop is a complete course about building the most optimal data models for your Power BI reports. It introduces the audience to the basic techniques of shaping data models in Power BI.
PL-300T00: Microsoft Power BI Data Analyst
This PL-300T00: Microsoft Power BI Data Analyst course will discuss the various methods and best practices that are in line with business and technical requirements for modeling, visualizing, and analyzing data with Power BI.
Turn your data into immediate impact.
Do more with less using an end-to-end BI platform to create a single source of truth, uncover more powerful insights, and translate them into impact with these recommended resources:
- The Common Questions and Answers for Power BI
- Why POWER BI makes your reporting life easier?
- Low-Code Datamart with Microsoft Power BI
- The Future Of Enterprise Analytics Is Power BI And Azure
- Microsoft Power BI: Creating Meaningful Data Reports
- Real-Time Streaming In Microsoft Power BI
- How Do You Extract The True Value Of Your Data With Power BI?
- Master The Art Of Learning Dax In 2023
- Why DAX Is Underrated And Why You Should Learn?