Microsoft Power BI Desktop: Advanced DAX for Data Analysis

Master data modeling fundamentals in Power BI by distinguishing data fact tables from lookup dimension tables, defining keys, applying one-to-many relationships, filter flow, star schema, and unidirectional filters.

Explore essential DAX fundamentals for Power BI, including operators, common functions, and the calculate function, plus how calculated columns, measures, and evaluation context shape results.

Introduce the Maven Roasters course project by loading raw CSV data into Power BI, building the data model with relationships, and using DAX to analyze sales, inventory, and budgets.

Learn to set up the Maven Roasters Power BI data model by importing CSVs, validating data, and wiring a five-dimension, five-lookup, and two fact-table model with one-to-many, unidirectional relationships.

Explore how the formula and storage Dax engines in a tabular model compress data, evaluate queries, and return calculated tables, columns, or measures.

Explore how the formula engine interprets and executes DAX queries via a query plan, while the storage engine compresses data into a data cache for Vertipaq in-memory storage.

Explore how DAX queries are evaluated in Power BI, tracing a measure through the formula engine, storage engine, and data cache as filter context updates trigger reevaluation.

Explains how Power BI assigns six data types—integers, decimals, currency, date/datetime, boolean, strings—and how each is stored, highlighting storage over formatting for compression and encoding.

Explore how Vertipaq compresses and encodes data to reduce memory and speed up DAX queries. Learn value encoding, hash encoding, and run-length encoding and how they combine for data models.

Learn how value encoding uses a mathematical relationship to compress integer columns, like zip codes, reducing storage from 14 to 9 bits and preserving data integrity, then explore hash encoding.

Explore hash encoding in Power BI's Vertipaq engine: build a dictionary of unique column values, assign indices, and store data as integers based on cardinality rather than string length.

Run length encoding stores values with counts, exploiting consecutive repeats sorted by Vertipaq engine, and can pair with hash or dictionary encoding to boost Power BI compression for DAX queries.

Understand how Vertipaq maps column relationships to fast filter context for Dax queries, using line IDs, index values, and dictionary encoding to optimize measures like quantity sold.

Discover how the DAX formula engine evaluates queries and the storage engine compresses data with Vertipaq and DirectQuery, using value encoding, hash or dictionary coding, and run length encoding.

Explore efficient DAX workflows using shortcuts, IntelliSense, and comments to craft measures. Organize measures in a dedicated measures table and use variables for readability and performance.

Master eight DAX shortcuts for Power BI, including inserting lines, duplicating rows, jumping to a line, and using square brackets to call measures.

Learn to comment Dax code in Power BI using single line and multi line comments to explain measures like sales by store and cost.

Learn how to create a dedicated measure table in Power BI, move DAX measures into it, and organize them with folders using display folders for easy access.

Explore error handling in Power BI DAX using if error and is blank to identify missing data and support QA testing, with practical examples in last-year sales measures.

Discover how DAX variables improve readability and performance by declaring reusable expressions with var and return, then build measures like orders by females using calculate and filters.

Learn variable evaluation order in DAX: once defined, variables lock in their values and cannot be modified by later filters; define logic inside the variable and use a return expression.

Learn to use variables in DAX to test and debug measures, perform safe division with divide, and inspect components like percent quantity sold to females.

Explore scalar functions that return a single value, including aggregation, information, conversion, rounding, and logical functions, with use cases like averaging values, max price, and sum of revenue.

Explore common scalar function categories in DAX, including aggregation, rounding, information, conversion, and logical functions, with examples like sum, average, if, and coalesce.

Explore aggregation functions in Power BI DAX, including sum, average, max, min, count, distinct count, and count rows; create total customers via distinct count and total employees via count rows.

Discover how sum and sumx are functionally the same at runtime, with sum read as sumx and serving as syntax sugar for iterator version of aggregation functions behind the scenes.

Explore rounding functions in DAX, including int, round, round up, round down, mround, truncate, fixed, ceiling, and floor. Learn to create time-based bins and apply precise rounding in Power BI.

Explore rounding functions to calculate customer ages by adding a new age column in the customers table and ensuring ages are whole numbers.

Create a new column to compute customer age with date diff from birth date to today, then floor rounding. Use a measure for updates and prefer floor over round down.

Explore information functions that reveal data types and filter context in DAX, use isblank, iserror, islogical, isnumber, and istext to inspect columns and build calculated columns and measures.

Master conversion functions in DAX, including currency, format, date, time, and date value, with date value automatically switching formats, and apply currency as a calculated column data type.

Create a calculated column in calendar table to format the transaction date as year, year, year, month, month, day, day and apply a conversion function to render cost as currency.

Create a calculated column to format transaction date as year, month, day, and apply a conversion function to the cost measure to display it as currency with rounding.

Learn to use the switch function in DAX to map a calendar month id to month names and handle unmatched values.

Discover how the Coalesce function in Power BI DAX replaces if and is blank patterns and returns the first non-blank argument, or blank if all are blank, while boosting performance.

Create a calculated column in the calendar table that combines quarter and year using variables and switch true, producing values like Q1 2020 for Power BI Desktop advanced DAX analysis.

The walkthrough demonstrates building a calculated column in the calendar table to produce quarter and year values using DAX variables, the IN function, and SWITCH.

Explore advanced calculate topics in DAX, including evaluation order, context transition, and modifiers, to master filter context and create percent of total or category measures.

Understand expanded tables in DAX, where a base table includes related columns from one-to-many relationships. See how filter context propagates across expanded tables in the data model.

Demonstrate context transition by turning row context into filter context with calculate, enabling correct aggregation in calculated columns and measures in a food inventory example.

Learn how calculate evaluates filter arguments in DAX by processing modifiers first, then filter parameters, and how all or keep filters influence store and product context in sales analysis.

Explore calculate modifiers that alter the filter context within the calculate function, including all, all selected, keep filters, remove filters, use relationships, and cross filter to adjust propagation.

Explore how remove filters clears filters from a table or columns in DAX, aliasing the all function and used as a calculate modifier (not table function) to compute total profit.

Keep filters adds new filter context to a calculate statement, effectively filtering by the current context and illustrating how store five profit behaves under different slicer selections.

Build matrix of product group and store id, use keep filters to create measures for three, five, and eight, and remove filters with variables to compute percent of store sales.

Build a matrix of product group and store id, then create three store sales measures with keepfilters. Add a percent of store sales measure using removefilters and variables.

Discover common calculate patterns in Power BI, including cumulative totals, overall totals, and percent of total sales, using filter context, all, and helpful variables.

Discover table and filter functions in dax to return columns or tables, create calculated tables, filter and manipulate data, and generate new data from scratch.

Explore common table and filter functions in Power BI DAX, covering filtering, adding columns and summaries, and creating data with table constructors and calculated tables.

Explore how DAX table arguments accept physical or virtual tables, and use filter and all to craft calculated tables, while observing how DAX engines create virtual tables during measure evaluation.

Explore the distinct function in Power BI DAX, which returns a single column table of unique values or unique combinations from a column or table, enabling lookup or dimension tables.

Explore the values function in DAX, which returns a full table or a single column of unique values, and understand how a blank row appears when related tables miss data.

Validate the ginger scone's presence in the data model, create a blank-row view using values and distinct functions, show missing product IDs, and update the model with the new product.

Learn how to use distinct and values in Power BI to reveal a blank product row, count unique product IDs, and update the data model with a new lookup entry.

Learn how the selected value DAX function in Power BI returns a single value from a column only when the filter context yields a unique value, with an alternate result.

Explain how all except removes filters on a base table while preserving filters on specified columns, using calculate with table and keys like transaction date and product group.

Demonstrates using allexcept to build a store eight 2018 top customers matrix, create a filtered measure, and compute the percent of store-level sales by the top ten.

Build a Power BI matrix for 2018 top ten customers at store eight, using ALLEXCEPT to craft a measure and calculate the percent of store level sales.

Explore the all selected function in Power BI DAX, which returns all rows or values while ignoring row and column filters, yet preserves external filter context, including the slicer.

Create measures for total baked and total sold, compute percent of total baked and percent of total sold with a product type slicer, and identify the top baked item.

Build total baked and total sold measures, then percent of total baked and sold using all selected, and compare these percentages across products in a store slicer matrix.

Learn the select columns function in Power BI DAX, which returns a table with selected columns and new columns from a given (physical or virtual) table, created by scalar expressions.

Add columns returns the original table with new columns defined by a DAX expression, unlike select columns. Use cases include adding columns to an existing or virtual table.

Explore the summarize function that groups a table by specified columns to return unique combinations, not aggregates, with a Power BI demo using physical or calculated tables.

Use summarize to create a table showing days with unsold pastries and the columns to calculate sold, unsold, and lost revenue; then build a matrix by product and store.

Use DAX summarize to create an unsold pastries table, calculating sold, unsold, and lost revenue by day and store, and analyze results with a matrix.

Explore four DAX techniques to generate data from scratch in Power BI, including row, data table, generate series, and table constructor—creating tables with custom columns and records.

Learn how the row function returns a one-row table with named columns built from a name and an expression. See examples using customer sales and quantity sold to build columns.

Create a fixed data table in Power BI with the data table function, defining column names and types and entering static data using curly braces.

Generate series creates a one-column table of sequential values from start to end, with an optional increment. It explains usage in dax queries and parameter inputs for building custom ranges.

Master building tables in Power BI Desktop with the table constructor, creating single-column, multi-row, and multi-column results from fixed values or scalar expressions, and note column headers cannot be named.

Recreate the target sales table for April 2019 by product group using a data table, aligning with Jean’s forecast and solving the assignment independently.

Generate a target sales table for April 2019 by product group in Power BI using the data table function, defining store id, year, month, beverage, food, and merchandise goals.

Explore calculated table joins to blend data across multiple tables using cross join, union, except, and intersect, and analyze new versus returning customers and repeat purchase behavior with advanced DAX.

Learn how cross join creates a Cartesian product of two tables to produce all combinations, with guidance on inputs, rows, columns, and practical examples in Power BI.

Use the union function to stack two tables with identical columns, where column names come from the first table and columns align by position, while preserving duplicates.

Explore the Intersect function in Power BI DAX to find rows common to two tables, using a left table and a right date-filtered table for previous-month active customers.

Use calculate table and intersect to identify returning customers across weeks 45 and 46, then add revenue and profit measures to quantify totals.

Master relationship functions in DAX to access fields across physical and virtual tables, including inactive relationships, cross-filter direction, and creating virtual relationships for calculated columns or measures.

Explore physical and virtual relationships in Power BI, where physical links are visible and typically one-to-many, offering best performance, while virtual links use DAX to connect tables at different granularities.

Explore the related function in Power BI Desktop, which returns values from a related table in a many-to-one relationship and accesses product group and current wholesale price.

Explore how related table returns a filtered related table, using many-to-one relationship and aggregation functions like count rows and sumx, with context transition and acting as shortcut for calculate table.

Learn to build a high value flag in Power BI by adding a calculated column to the customer lookup table and using related and relatedtable to compare sales against average.

Add a calculated column in the customer lookup table to classify customers as high or low value using total sales and average sales, with a high-low flag from related tables.

Use relationship enables evaluating a DAX expression across an inactive relationship by specifying foreign and primary keys. Use with calculate-style filters to traverse multiple date fields toward a calendar table.

Use the cross filter function to control filter flow between related tables by naming the left and right columns and selecting a cross filter type (unidirectional or bidirectional).

Develop advanced DAX techniques to measure customers who purchased using countrows and filter, compute the average order value, and present results in a matrix by product group.

Explore advanced dax in Power BI to create customers who purchased with count rows and cross filter, compute average order value, and visualize by month and product group.

Explore how treat as transfers filter context between tables to form a virtual relationship, mapping a table expression to columns, with calendar year and calendar month and store IDs examples.

Create three-day measures for bean tea, beverage, merchandise, and food sales goals from sales union table. Add percent-of-goal measures and display them in a matrix by store and target month.

Apply treatas to map calendar year and month to the union demo table, creating percent-to-goal measures and a store-by-month matrix for B.A. beverage, merchandise, and food goals.