Power BI: DAX and Data Modeling
Star schemas, measures, CALCULATE and time intelligence.
Taught by Niraj Thapa · Analytics Lead
What you'll learn
- Design a star schema with conformed dimensions, surrogate keys and a marked date table
- Decide correctly between calculated columns, measures and Power Query transformations
- Explain row context and filter context and predict what a measure returns in any visual
- Use CALCULATE, ALL, REMOVEFILTERS, KEEPFILTERS and USERELATIONSHIP to reshape filter context
- Write iterator measures with SUMX and AVERAGEX and structure them with variables
- Build time-intelligence measures (YTD, prior year, rolling averages) and tune model performance with DAX Studio
About this course
DAX and data modelling are the hard part of Power BI and the part that decides whether a report is fast, correct and maintainable. This intermediate course is for analysts who can already build basic Power BI reports and want to master the model and the language behind it. You will design proper star schemas, choose between calculated columns and measures, understand row context and filter context, use CALCULATE with confidence, write iterators and variables, build a robust date table with time-intelligence measures such as year-to-date and year-over-year, and diagnose slow reports with DAX Studio and Performance Analyzer. The material maps closely to the modelling and DAX sections of the PL-300 Power BI Data Analyst exam and is equally useful for Excel Power Pivot. You need Power BI Desktop and a working knowledge of relationships, visuals and simple SUM measures. Independent study material — not affiliated with or endorsed by Microsoft.
Course content
6 lessons- 1Star Schemas: Why the Model Comes FirstFacts, dimensions, keys and the modelling decisions that make DAX simple and fast.Preview
- 2Calculated Columns, Measures and Where Logic BelongsChoose the right layer for each calculation and understand how each is stored and evaluated.
- 3Evaluation Context: Row Context and Filter ContextThe two contexts that decide what every DAX expression returns, and the rules of context transition.
- 4CALCULATE: The Engine of DAXAdd, replace and remove filters with CALCULATE, ALL, REMOVEFILTERS, KEEPFILTERS and USERELATIONSHIP.
- 5Iterators, Variables and Time IntelligenceSUMX and friends, VAR for clarity, and the date-table measures every report needs.
- 6Performance: DAX Studio, Storage Engine and Model SizeFind slow visuals, read query timings, shrink the model and apply the fixes that matter.