Conquering DAX 2019

Course Outline

Reading Material

Download

Day

Modules

References

1

Day1.zip
Day1-Solution.zip

Exercises		Materials
1	Using Variables	Excel Model: Financial.xlsx
2	CROSSFILTER Function	Solving the Both direction issue in report page #1
3	RELATED and RELATEDTABLE Functions	Power BI RelatedTable using a simple example.docx

What is DAX?
DAX data types
Calculated columns
Measures
Aggregation functions
Counting values
Conditional functions
Handling errors
Using variables
Mathematical functions
Relational 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

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

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

2

Day2.zip
Day2-Solution.zip

Exercises		Materials
1	Calculate Function	Instruction Data
2	Replicate VLookup	Instruction Data
3	Related Function	Instruction Data
4	Switch Function	Instruction Data

CALCULATE modifiers
USERELATIONSHIP function
CROSSFILTER function
ALL function
ALLSELECTED function
KEEPFILTERS function
Context transition
Circular dependency
CALCULATE execution order

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

3

Day3.zip
Day3-Solution.zip
Date Table in Power BI.pptx
Expended Table.pptx

Exercises		Materials
1	Running Total	Instruction Data

Introduction to date tables
Auto Date/Time
CALENDARAUTO function
Mark as date table
Using multiple dates

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

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

What is data lineage?
TREATAS function

Filters are tables!
Difference between base tables and expanded tables
Filtering a column

Expanded tables in DAX

Day4.zip
Day4-Solution.zip
DAX Patterns.pptx
Patterns Files (PBI).zip

What are arbitrarily shaped filters?
Example of an arbitrarily shaped filter

ALLSELECTED function revisited
Shadow filter contexts

Static segmentation
Circular dependency in calculated tables
Dynamic segmentation

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

5

Day5.zip
Day5-Solution.docx

Understanding ambiguity

Bidirectional relationships and ambiguity in DAX

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

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 function
Query measures

Recommanded Books
	Title	Teach Yourself Visually Power BI
	ISBN	978-1-119-90377-2
	Author	Alexander Loth
	Year	2023
	Publisher	Visual
	Title	Data Cleaning with Power BI: The definitive guide to transforming dirty data into actionable insights
	ISBN	978-1-805-12640-9
	Author	Gus Frazer
	Year	2024
	Publisher	Packt Publishing
	Title	The Complete Power BI Interview Guide: A modern approach to acing the data analyst interview and landing your dream job
	ISBN	978-1-805-12067-4
	Author	Sandielly Ortega Polanco, Gogula Aryalingam and Abu Bakar Nisar Alvi
	Year	2024
	Publisher	Packt Publishing Pvt Ltd
	Title	Effective Strategy Execution: Business Intelligence Using Microsoft Power BI (Management for Professionals)
	ISBN	978-3-662-68806-9
	Author	Bernd Heesen
	Year	2024
	Publisher	Springer
	Title	Pro Power BI Architecture: Development, Deployment, Sharing, and Security for Microsoft Power BI Solutions
	ISBN	978-1-484-29537-3
	Author	Reza Rad
	Year	2023
	Publisher	Apress
	Title	Mastering Microsoft Power BI: Expert techniques to create interactive insights for effective data analytics and business intelligence, 2nd Edition
	ISBN	978-1-801-81148-4
	Author	Greg Deckler, Brett Powell
	Year	2022
	Publisher	Packt Publishing
	Title	Up and Running with DAX for Power BI: A Concise Guide for Non-Technical Users
	ISBN	978-1-484-28187-1
	Author	Alison Box
	Year	2022
	Publisher	Apress
	Title	Pro Power BI Dashboard Creation: Building Elegant and Interactive Dashboards with Visually Arresting Analytics
	ISBN	978-1-484-28226-7
	Author	Adam Aspin
	Year	2022
	Publisher	Apress
	Title	Pro Data Mashup for Power BI: Powering Up with Power Query and the M Language to Find, Load, and Transform Data
	ISBN	978-1-484-28577-0
	Author	Adam Aspin
	Year	2022
	Publisher	Apress
	Title	Power BI Data Modeling: Build Interactive Visualizations, Learn DAX, Power Query, and Develop BI Models
	ISBN	978-9-389-32883-7
	Author	Nisal Mihiranga
	Year	2022
	Publisher	BPB Publications
	Title	Power BI for Jobseekers: Learn how to create interactive dashboards and reports, and gain insights from the data
	ISBN	978-9-355-51814-9
	Author	Alan Murray
	Year	2023
	Publisher	BPB Publications
	Title	Learning Microsoft Power BI: Transforming Data into Insights
	ISBN	978-1-098-11284-4
	Author	Jeremey Arnold
	Year	2023
	Publisher	O'Reilly Media
	Title	Applied Microsoft Power BI: Bring your data to life!
	ISBN	978-1-733-04613-8
	Author	Teo Lachev,Edward Price,Maya Lachev,Martin Lachev
	Year	2022
	Publisher	Prologika
	Title	Microsoft Power BI For Dummies
	ISBN	978-1-119-82487-9
	Author	Jack A. Hyman
	Year	2022
	Publisher	For Dummies
	Title	Microsoft Power BI Performance Best Practices: A comprehensive guide to building consistently fast Power BI solutions
	ISBN	978-1-801-07644-9
	Author	B. Merchant, C. Webb
	Year	2022
	Publisher	Packt Publishing
	Title	Learn Power BI: A comprehensive, step-by-step guide for beginners to learn real-world business intelligence, 2nd Edition
	ISBN	978-1-801-81195-8
	Author	Greg Deckler
	Year	2022
	Publisher	Packt Publishing
	Title	Introducing Charticulator for Power BI: Design Vibrant and Customized Visual Representations of Data
	ISBN	978-1-484-28075-1
	Author	A. Box
	Year	2022
	Publisher	Apress
	Title	Extreme DAX: Take your Power BI and Microsoft data analytics skills to the next level
	ISBN	978-1-801-07851-1
	Author	Michiel Rozema, Henk Vlootman
	Year	2022
	Publisher	Packt Publishing
	Title	Mastering Power BI: Build Business Intelligence Applications Powered with DAX Calculations, Insightful Visualizations, Advanced BI Techniques, and Loads of Data Sources
	ISBN	978-9-391-03072-8
	Author	Chandraish Sinha
	Year	2021
	Publisher	BPB Publications
	Title	The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel
	ISBN	978-1-509-30697-8
	Author	Marco Russo, Alberto Ferrari
	Year	2019
	Publisher	Microsoft Press
	Title	Supercharge Power BI: Power BI Is Better When You Learn to Write DAX
	ISBN	978-1-615-47069-3
	Author	Matt Allington
	Year	2018
	Publisher	Holy Macro! Books
	Title	Power BI: Moving Beyond the Basics of Power BI and Learning about DAX Language
	ISBN	B089XYQCVR
	Author	Mike Morris, Nick Gallagher, et al.
	Year	2020
	Publisher	Albano Publishing LLC
	Title	DAX Cookbook: Over 120 recipes to enhance your business with analytics, reporting, and business intelligence
	ISBN	978-1-839-21707-4
	Author	Greg Deckler
	Year	2020
	Publisher	Packt Publishing
	Title	Hands-On Business Intelligence with DAX
	ISBN	978-1-838-82863-9
	Author	Ian Horne
	Year	2020
	Publisher	Packt
	Title	Applied DAX with Power BI: From zero to hero with 15-minute lessons
	ISBN	978-1-733-04610-7
	Author	Lachev, Teo
	Year	2019
	Publisher	Prologika Press
	Title	DAX Patterns
	ISBN	978-1-735-36520-6
	Author	Russo, Marco; Ferrari, Alberto
	Year	2020
	Publisher	SQLBI Corp.
	Title	Supercharge Excel: When you learn to Write DAX for Power Pivot
	ISBN	978-1-615-47053-2
	Author	Matt Allington
	Year	2018
	Publisher	Holy Macro! Books
	Title	Pro DAX with Power BI: Business Intelligence with Powerpivot and SQL Server Analysis Services Tabular
	ISBN	978-1-484-24896-6
	Author	Philip Seamark; Tom Martens
	Year	2019
	Publisher	Apress
	Title	Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence
	ISBN	978-1-484-23476-1
	Author	Philip Seamark
	Year	2018
	Publisher	Apress