Description
WHAT YOU WILL LEARN?
This 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. The training 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.
OBJECTIVE
After completing this course, students will be able to:
- Understand all the features of the DAX language
- Write formulas for common and advanced scenarios
PREREQUISITES
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.
COURSE CONTENT
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
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
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
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
5. Advanced Evaluation Contexts
- CALCULATE modifiers
- USERELATIONSHIP function
- CROSSFILTER function
- ALL function
- ALLSELECTED function
- KEEPFILTERS function
- Context transition
- Circular dependency
- CALCULATE execution order
6. Iterators
- Working with iterators
- MINX and MAXX functions
- Useful iterators
- RANKX function
- ISINSCOPE function
7. Building a Date Table
- Introduction to date tables
- Auto Date/Time
- CALENDARAUTO function
- Mark as date table
- Using multiple dates
8. 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
9. Hierarchies in DAX
- What are hierarchies?
- FILTER and CROSSFILTER function
- Percentages over hierarchies
- Parent-child hierarchies
10. Data Lineage and TREATAS
- What is data lineage?
- TREATAS function
11. Expanded Tables
- Filters are tables!
- Difference between base tables and expanded tables
- Filtering a column
12. Arbitrarily Shaped Filters
- What are arbitrarily shaped filters?
- Example of an arbitrarily shaped filter
13. ALLSELECTED and Shadow Filter Contexts
- ALLSELECTED function revisited
- Shadow filter contexts
14. Segmentation
- Static segmentation
- Circular dependency in calculated tables
- Dynamic segmentation
15. Many-to-many Relationships
- How to handle many-to-many relationships
- Bidirectional filtering
- Expanded table filtering
- Comparison of the different techniques
16. Ambiguity and Bidirectional Filters
17. Relationships at Different Granularities
- Working at different granularity
- Using TREATAS function
- Calculated tables to slice dimensions
- Leveraging weak relationships
- Scenario recap
- Checking granularity in the report
- Hiding or reallocating
18. Querying with DAX
- Working with tables and queries
- EVALUATE syntax
- CALCULATETABLE function
- SELECTCOLUMNS function
- SUMMARIZE function
- SUMMARIZECOLUMNS function
- CROSSJOIN function
- TOPN and GENERATE functions
- ROW and DATATABLE functions
- Tables and relationships
- UNION, INTERSECT and EXCEPT functions
- GROUPBY functions
- Query measures
