Microsoft Power BI Comprehensive: Learn 5+ Complete Projects

In this lecture, we introduce Section 2: Data Preparation in Power BI Desktop.

You’ll get an overview of what this section covers, including techniques for importing, cleaning, and transforming data using Power Query. This section lays the foundation for building accurate and reliable data models in Power BI.

By the end of this lecture, you’ll know what to expect in this section and how these skills will help you prepare data effectively for analysis and visualization.

In this lecture, we explore the three main views in Power BI Desktop—Report View, Table (Data) View, and Model View—and their roles in building effective BI solutions. You’ll also learn how to use the Power Query Editor for data preparation and transformation before loading data into your model.

By the end of this lecture, you’ll understand how these views work together, when to use each one, and how Power Query simplifies data cleaning and shaping for analysis.

In this lecture, you’ll learn about the different types of data sources compatible with Power BI, including flat files, databases, online services, and other advanced sources. This will help you understand how Power BI connects to diverse data for analysis and reporting.

In this lecture, you’ll learn that Power BI supports over 190 data connectors, enabling integration with a wide range of sources such as databases, Azure services, online platforms, and Power Platform connectors.

This flexibility allows you to connect and analyze data from virtually anywhere.

In this lecture, you’ll learn about the three storage modes in Power BI: Import, DirectQuery, and Composite. Understanding these modes is essential for managing data performance and connectivity in your Power BI projects.

In this lecture, you’ll learn why Import Storage Mode is the default method in Power BI and how it works.

We’ll discuss its advantages for performance and offline access when building reports.

In this lecture, you’ll learn about important precautions when using Import Storage Mode in Power BI.

We’ll cover considerations like file size, refresh limitations, and performance impacts to help you use this mode effectively.

In this lecture, you’ll learn about DirectQuery Storage Mode in Power BI, which connects directly to relational databases without importing data. We’ll cover its benefits and when to use it for real-time data access.

In this lecture, you’ll learn about situations where DirectQuery mode is required in Power BI. We’ll discuss scenarios like real-time data access, large datasets, and compliance needs that make DirectQuery the best choice.

Learn essential precautions when using DirectQuery storage mode in Power BI. This lecture explains performance considerations, limitations, and best practices to ensure efficient and reliable data modeling.

Understand Composite Storage Mode in Power BI, which combines Import and DirectQuery methods. Learn how this hybrid approach works and when to use it for optimal performance and flexibility.

In this lecture, you will start the course project by importing the Adventure Works dataset into Power BI using Import storage mode. The dataset includes key tables such as Currency Rate, Currency, Customer, Date, Product, Reseller, Sales Order, Sales Territory, and Sales.

You will learn how to:

• Load multiple tables into Power BI.

• Ensure correct data types and promote header rows.

• Prepare the data model for analysis and reporting.

• Understand the role of Import mode in performance optimization.

By the end of this lecture, you will have a fully imported dataset ready for building insightful reports and dashboards.

Learn the basics of Power Query Editor in Power BI, including its interface and essential data transformation steps to clean and prepare your data for analysis.

Explore key query editing tools in Power Query Editor, including the Home, Transform, and Add Column tabs.

Learn how to manage data sources, apply transformations, and create new columns for advanced data shaping.

Learn the most common data transformation techniques in Power Query Editor, including removing duplicates, splitting columns, changing data types, and handling missing values to prepare clean and structured data for analysis.

Practice applying common data transformations in Power Query Editor, including removing unnecessary columns, renaming fields, correcting data types, handling missing values, filtering rows, removing duplicates, splitting/merging columns, and ensuring data consistency.

This lecture provides a Q&A exercise designed to help you reflect on your learning of common data transformations in Power Query Editor. Review key questions on importing data, verifying data types, renaming columns, and handling missing values to reinforce your understanding.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

Learn about the 3 types of data profiling tools in Power Query Editor — Column Quality, Column Distribution, and Column Profile. Understand how these tools help assess data completeness, detect errors, and ensure consistency before analysis.

Learn how to use the Column Quality tool in Power Query Editor to assess data accuracy and completeness. Understand its five indicators — Valid, Error, Empty, and related metrics—to ensure clean and reliable data for analysis.

Learn how to use the Column Distribution tool in Power Query Editor to analyze data patterns and cardinality levels. Understand how this feature helps identify unique values, detect inconsistencies, and improve data quality before modeling.

Learn how to use the Column Profile tool in Power Query Editor to explore detailed column statistics, including distribution, distinct values, and data patterns. This helps ensure data quality and consistency before analysis.

This is a Q&A exercise designed to help you reflect on your learning of data profiling tools in Power Query Editor. Answer practical questions using Column Quality and Column Profile tools to check for empty values, calculate averages, identify price ranges, and analyze product and reseller counts.

Discover common errors that occur when importing data into Power BI, such as query timeouts, missing files, incorrect table formatting, and data type mismatches. Learn how to identify these issues and understand their impact on your data loading process.

Learn how to use Text Tools in Power Query Editor to clean and format text data. This includes operations like trimming spaces, changing case, extracting substrings, and combining text for better data consistency.

Practice using Text Tools in Power Query Editor with real-world scenarios on the "OfficeSupplies.csv" dataset. Apply functions like Capitalize Each Word, Extract Before Delimiter, and Merge/Split Columns to clean and format text data for better analysis.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this DIY exercise, you will apply Text Tools in Power Query Editor to clean and transform data. Tasks include importing the Customer dataset, promoting headers, setting correct data types, formatting names using proper case, merging columns to create a Full Name and Full Address, extracting text after delimiters, creating a conditional column for continent mapping, trimming whitespaces, and splitting columns for testing.

Hint: Continue on your Adventure Works Lecture Project File to complete this DIY Exercise!

Learn how to use Numerical Tools in Power Query Editor to perform calculations and transformations on numeric data. Explore options like statistical functions (Sum, Average, Median), rounding methods, scientific operations (Power, Square Root, Logarithm), trigonometric functions, and information tools for flags such as even/odd or positive/negative values.

Practice using Numerical Tools in Power Query Editor with the “OfficeSupplies.csv” dataset. In this exercise, you will calculate averages, create new columns using multiplication, apply rounding to numeric fields, count distinct values, adjust prices by a percentage increase, and compute updated total sales. These tasks reinforce your understanding of statistical and standard numeric functions.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this DIY exercise, you will apply Numerical Tools in Power Query Editor using the Adventure Works Product dataset. Tasks include importing and preparing the Product table, verifying data types, calculating metrics such as average listed price, product count, color varieties, and price ranges. You will also create calculated columns for price adjustments, format values, round decimals, compute averages by subcategory, and replace null values for clean data.

Hint: Continue on your Adventure Works Lecture Project File to complete this DIY Exercise!

Learn how to use Date/Time Tools in Power Query Editor to manage and transform date and time fields. Explore options like extracting year, month, day, and time components, creating custom date columns, and formatting date values for accurate time-based analysis.

In this practice exercise, you will use Date/Time Tools in Power Query Editor with the Financial Sample.xlsx dataset.

Tasks include extracting components from a date column such as Year, Month Name, Day Name, Start of Week, Start of Month, and Week of Year.

You will also calculate the age of each date compared to the current date, create a duration column, and convert it into total days using the Duration functions. This hands-on session reinforces your ability to prepare time-based data for analysis.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this DIY exercise, you will work with the Adventure Works Date table using Power Query Editor.

Tasks include verifying data types and applying Date/Time tools to create new columns such as Name of Day, Start of Month, Name of Month, Start of Week, Quarter of Year, and Year. This hands-on activity ensures your data is properly structured for time-based analysis and reporting.

Hint: Continue on your Adventure Works Lecture Project File to complete this DIY Exercise!

There are some cases where you will commonly face error messages when formatted as date data type. This is typically because of the wrong locale with the current date values you are using.

For example, the U.S commonly uses "MM/DD/YYYY" format for dates, while the rest of the world commonly uses "DD/MM/YYYY". So when you typically imported date column with U.S format, but your Power BI solution settings are in a different another format, this will cause errors.

In this lecture, you will see how to check these errors and fix these problems.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

Learn how to create a complete Calendar Table in Power BI using an M Code template.

This specialized table includes a continuous range of dates with columns like Fiscal Year, Fiscal Quarter, Month, and Day Name, enabling advanced time intelligence calculations and date-based filtering. No hard-coding required—just follow the provided template for quick setup.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

Learn how to use the Group By feature in Power Query Editor to aggregate and summarize data efficiently.

This lecture covers grouping operations at both column and row levels, allowing you to transform detailed data into higher-level summaries such as monthly or yearly totals, and group sales by categories or locations.

You will also explore common aggregation functions like Sum, Average, Median, Min, Max, Percentile, and Count.

Practice using the GROUP BY feature in Power BI with the Power Query Editor.

In this exercise, you’ll learn how to group data by one or multiple columns and apply aggregations like Average Fare and Max Age using the Titanic dataset. This hands-on activity will help you master grouping and aggregating techniques for real-world scenarios.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this hands-on exercise, you’ll apply the GROUP BY and Advanced Group By features in Power BI using the Adventure Works dataset.

You will learn how to group data by multiple columns and calculate aggregations such as Average Listed Price, Average Standard Cost, and Maximum/Minimum prices.

By completing this lecture, you’ll gain confidence in handling more complex grouping and aggregation scenarios in Power Query Editor.

Hint: Continue on your Adventure Works Lecture Project File to complete this DIY Exercise!

Learn how to add Index Columns in Power BI using Power Query. This feature creates sequential position values to support data transformation. By the end of this lecture, you will know how to:

• Add an index column starting from 0 or 1.

• Customize index settings for your data needs.

In this practice session, you will apply what you learned about Index Columns in Power BI.

Using the provided dataset, you will create index columns, customize starting points and increments, and understand how they support data transformation. By the end, you will confidently implement index columns in real-world scenarios.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

Learn how to create Conditional Columns in Power BI using Power Query. This feature allows you to apply logic-based conditions to generate new columns dynamically.

In this practice session, you will create a new column called Final Price based on the value in the Customer Group field using conditional logic in Power BI.

You will learn how to apply multiple conditions to select the correct price tier (Tier 1, Tier 2, or Tier 3) for each customer. By the end, you will be able to implement conditional columns for dynamic pricing scenarios.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this DIY exercise, you will create a conditional column named Continent in the Customer table.

Using the Country-Region field, you will assign continent values based on predefined mappings. By the end of this lecture, you will be able to implement custom conditional logic for geographic data classification in Power BI.

Hint: Continue on your Adventure Works Lecture Project File to complete this DIY Exercise!

In this lecture, you will perform advanced data transformation tasks on the Adventure Works project.

Activities include importing and connecting tables such as Sales Order, Sales Territory, Reseller, and Currency, verifying data types, promoting headers, and creating custom columns like Sales Order and Sales Order Line.

By the end, you will be able to manage complex data preparation steps and ensure accurate data modeling in Power BI.

Learn how to combine multiple queries in Power BI using Merge and Append operations. This lecture explains the difference between merging tables (joining columns) and appending tables (stacking rows) in Power Query.

In this lecture, you will learn how to append multiple tables into a single table using Power Query in Power BI.

This technique is useful for combining datasets with similar structures into one unified table for analysis.

In this practice session, you will work with three datasets: Online Sales, Store Sales, and Wholesale Sales.

You will learn how to append two tables first and then extend the process to append all three tables into one combined dataset using Power Query. By the end, you will be able to efficiently consolidate multiple tables for unified analysis in Power BI.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this do-it-yourself exercise, you will import and connect multiple yearly sales tables (Sales 2017, Sales 2018, Sales 2019, and Sales 2020) into your Adventure Works project.

After verifying data types and promoting headers, you will append all tables into a single consolidated query named Sales 2017-2020.

By the end, you will be able to confidently perform multi-table appending for large datasets in Power BI.

Hint: Continue on your Adventure Works Lecture Project File to complete this Exercise!

In this practice session, you will learn a faster method to append multiple datasets with the same structure using the Import from Folder feature in Power BI.

You will organize sales files from different years into a single folder, ensure consistent sheet names, and use the Combine & Transform Data option to append all files at once.

By the end, you will be able to efficiently consolidate multiple files into one query for streamlined analysis.

Hint: Continue on your Adventure Works Lecture Project File to complete this Exercise!

In this lecture, you will learn how to merge multiple tables in Power BI using Power Query.

Merging combines columns from different tables based on matching keys, allowing you to enrich your dataset with related information.

In this lecture, you will explore the different types of joins available in Power Query when merging tables, including Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, and others.

You will learn how each join type affects the resulting dataset and when to use them.

By the end, you will be able to choose the appropriate join type for your data integration needs.

In this practice session, you will merge multiple tables using different join types in Power Query.

You will work with real datasets to apply Inner Join, Left Outer Join, and other join methods, ensuring accurate data integration.

By the end, you will be able to confidently merge tables based on key columns and choose the right join type for your analysis.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this lecture, you will learn how to use Fuzzy Merge in Power Query to match and merge tables when key values are similar but not identical.

This technique is useful for handling typos, inconsistent naming, or partial matches across datasets. By the end, you will be able to configure similarity thresholds and apply fuzzy matching for accurate data integration.

In this practice session, you will apply Fuzzy Merge in Power Query to handle non-standardized data. Using a survey dataset with typos, plural forms, and inconsistent casing, you will merge it with a reference table to standardize values.

By the end, you will be able to configure similarity thresholds and use fuzzy matching to clean and unify messy data.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this lecture, you will learn the concepts of Pivoting, Unpivoting, and Transpose in Power Query. These transformations help reshape your data for better modeling and reporting.

By the end, you will understand when and how to apply these techniques to organize columns and rows effectively in Power BI.

In this practice session, you will work with a dataset to apply Pivot and Unpivot transformations in Power Query.

You will learn how to convert row values into column headers using the Pivot function and reverse the process with Unpivot for flexible data reshaping.

By the end, you will be able to organize data effectively for reporting and analysis in Power BI.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this DIY Exercise, you will work with a matrix dataset containing Total Units Sold, Total Revenue, and Total COGS from 2019 to 2024. You will use Pivoting and Unpivoting functions to reshape the data and then replicate the same result using the Transpose method.

By the end, you will master multiple techniques for reorganizing data structures in Power BI.

Hint: Create a new Power BI Solution File to Complete this Exercise!

In this lecture, you will learn the concept of Query Folding in Power BI. Query Folding refers to the process where Power Query transformations are translated into native queries for the data source, improving performance and efficiency.

Although not heavily used in day-to-day tasks, understanding this concept is essential for PL-300 exam preparation and optimizing data refresh operations.

In this lecture, you will learn how to manage Data Source Settings in Power BI. This is essential when relocating your Power BI solution files or datasets, as moving files can break existing connections.

You will explore how to update file paths and restore connectivity to ensure smooth data refresh and report functionality.

In this lecture, you will learn how to manage query refresh settings in Power BI.

You will explore options to enable or disable query loading into Power BI Desktop and exclude queries from report refresh when they are not needed or updated frequently.

By the end, you will be able to optimize refresh performance and control which queries are included in your reports.

In this lecture, you will learn best practices to improve query performance in Power BI.

Topics include trimming unnecessary data, checking data types, reducing cardinality, disabling query load, grouping queries in Power Query Editor, and using parameters for reusable values.

By the end, you will be able to optimize your data model for faster refresh and better efficiency.

This introduction provides an overview of the section on Data Modeling Techniques in Power BI.

Students will learn the importance of data modeling, understand key concepts such as relationships, cardinality, and normalization, and prepare for hands-on exercises that demonstrate how to build efficient and scalable data models.

By the end of this section, students will be ready to apply best practices for creating optimized models in Power BI.

Get introduced to the Model View in Power BI. Learn how to visualize data models, explore relationships between tables, and use the Model Explorer for better organization.

Understand what data modeling is and what a data model represents. Learn how data models define relationships between data elements and how data modeling helps organize and structure business data.

Discover the key benefits of data modeling in Power BI, including improved collaboration, better data integrity, faster analytics, and enhanced business decision-making.

Learn about the three main types of data modeling concepts: conceptual, logical, and physical and understand their roles in structuring and organizing data effectively.

When learning the data modeling concept, understanding the components of a typical data model is very crucial. This is the reason why the concepts of Fact Tables and Dimension Tables are introduced in this topic.

You will also learn the purpose of each type of Table, how it is structured, and its usage and role in data schemas.

By understanding this topic, students can further understand the nature of schemas: Star Schemas, Snowflake Schemas, Galaxy Schemas etc., which are further used in the data modeling topics of Power BI.

In this practice session, students will learn how to identify fact tables and dimension tables within a data model.

By the end of the lecture, students will be able to distinguish between tables that store transactional data (facts) and those that provide descriptive attributes (dimensions), ensuring proper data modeling for analytics.

Hint: Continue on your Adventure Works Lecture Project File to complete this Exercise!

In this lecture, students will learn how filters work and propagate through relationships in a Power BI data model. By the end of the session, students will understand how filter context flows across related tables and how it impacts calculated results, enabling accurate analysis and reporting.

In this lecture, students will explore the concepts of database normalization and denormalization, including their roles in reducing redundancy, preserving data integrity, and improving query performance.

By the end of the session, students will understand how these concepts influence data modeling and why they are important for designing efficient Power BI models.

In this lecture, students will learn the concept of relationships in a data model and how they enable queries across multiple tables.

By the end of the session, students will understand how relationships propagate filters, the role of cardinality, and how DAX functions like RELATED, USERELATIONSHIP, and CROSSFILTER interact with relationships in Power BI.

In this lecture, students will learn the role of primary keys and foreign keys in a relational data model.

By the end of the session, students will understand how primary keys uniquely identify records, how foreign keys establish relationships between tables, and why these concepts are essential for building accurate and efficient Power BI data models.

In this lecture, students will learn about relationship cardinality in data models, including one-to-one, one-to-many, many-to-one, and many-to-many relationships.

By the end of the session, students will understand how cardinality is determined by data uniqueness, how it impacts filter propagation, and why selecting the correct cardinality is critical for accurate Power BI reporting.

In this hands-on exercise, students will practice creating relationships in a Power BI data model using a sample dataset as attached.

By the end of the session, students will be able to connect tables using primary and foreign keys, establish one-to-many relationships, and apply both methods: using the “Manage Relationships” dialog and drag-and-drop in Model View.

This exercise reinforces concepts of relational modeling and star schema design.

Hint: Create a new Power BI Solution File to Complete this Practice Exercise!

In this lecture, students will explore key DAX functions that are essential for managing relationships in Power BI models.

By the end of the session, students will understand how functions like RELATED, RELATEDTABLE, USERELATIONSHIP, CROSSFILTER, COMBINEVALUES, and TREATAS work, and how they can be applied to control filter propagation, create virtual relationships, and handle advanced modeling scenarios.

In this lecture, students will learn the difference between active and inactive relationships in a Power BI data model.

By the end of the session, students will understand how active relationships automatically propagate filters, why inactive relationships exist, and how to activate them using the USERELATIONSHIP( ) DAX function for advanced modeling scenarios.

This is a short practice session to review what you learned about active and inactive relationships in Power BI.

You will answer questions and see examples of how these relationships affect filtering between tables and how to activate an inactive relationship using the USERELATIONSHIP( ) function.

Hint: Create a new Power BI Solution File to Complete this Exercise!

In this lecture, you will learn what a data warehouse schema is and why it is important.

A schema is like a blueprint that shows how data tables are organized and connected.

You will also understand the basic components of schemas, such as fact tables and dimension tables, and how they help in building a strong Power BI data model.

In this lecture, you will learn about the three main types of data warehouse schemas: Star, Snowflake, and Galaxy. You will understand how each schema is structured, their differences, and the pros and cons of using them in Power BI data modeling.

In this lecture, you will learn the concept of the Star Schema in data modeling. You will see how a fact table is placed at the center and connected to multiple dimension tables.

This design is widely used in Power BI because it makes data models faster and easier to work with.

In this lecture, you will learn about the Snowflake Schema, which is an extended and more normalized version of the Star Schema.

You will see how it organizes data into a web-like structure with sub-dimension tables, making it useful for large datasets, reducing redundancy, and saving storage space.

In this lecture, you will learn about the Galaxy Schema, also called the Fact Constellation Schema. This schema uses multiple fact tables connected to shared dimension tables, making it ideal for analyzing different business processes in one model.

You will understand why it is suitable for large-scale enterprise systems and how it reduces redundancy through normalization.

In this practice session, you will work on connecting tables in the Adventure Works Power BI data model.

You will identify fact and dimension tables, arrange them in a proper schema, and create relationships.

By the end of this exercise, you will know how to confirm cardinalities, manage filter directions, and format data for accurate analysis.

Hint: Continue on your Adventure Works Lecture Project File to complete this Exercise!

In this lecture, you will learn about circular relationships in Power BI data models and why they cause errors.

You will see an example of a circular dependency, understand how it happens when columns depend on each other, and learn how to fix it using the REMOVEFILTERS( ) function in DAX.

In this lecture, you will learn about cross-filter directions in Power BI relationships.

You will understand how filters flow between tables, the difference between single-direction and bi-directional filters, and when to use each option based on relationship cardinality.

Hint: Continue on your Adventure Works Lecture Project File to complete this Exercise!

In this lecture, you will learn about the problems caused by bi-directional cross-filter relationships in Power BI.

You will understand why using both directions can create ambiguity in filter paths, lead to performance issues, and sometimes cause errors when setting up relationships.

In this lecture, you will learn when to use single cross-filter direction in Power BI.

You will understand why one-way filtering is recommended for hierarchical relationships, controlling data flow, simplifying relationships, and specific analysis scenarios.

In this lecture, you will learn about hierarchical relationships in Power BI.

You will see how to organize data in dimension tables by creating hierarchies, such as Category → Subcategory → Product or Year → Month → Day.

This helps you drill down into details and analyze data at different levels.

In this practice session, you will learn how to create hierarchies in Power BI using the Products dimension table.

You will organize fields like Category, Subcategory, and Product into a hierarchy and apply it in visual reports.

By the end of this exercise, you will know how to use Drill Up and Drill Down features to analyze data at different levels.

In this lecture, you will learn how to specify data categories and formats in Power BI.

You will understand why setting the correct category (such as Country, State, or Postal Code) helps Power BI display data correctly in visuals like maps.

By the end of this session, you will know how to improve your reports by defining proper data categories and formats.

In this practice session, you will learn how to set data categories and formats in Power BI using the Adventure Works project.

You will format columns like City, State, Country, Postal Code, and Continent with the correct data categories and update date fields to the proper format. This exercise ensures your visuals display data accurately.

Hint: Continue on your Adventure Works Lecture Project File to complete this Exercise!

In this lecture, you will review all the key concepts of data modeling in Power BI.

The summary includes schema design (fact and dimension tables), relationships and cardinality, normalization vs. denormalization, cross-filter directions, role-playing dimensions, data granularity, and performance optimization tips.

By the end of this session, you will have a clear understanding of best practices for building efficient Power BI models.

This lecture introduces Section 4 of the Microsoft Power BI Comprehensive Course.

You’ll get an overview of creating calculations using DAX, including calculated columns, measures, and essential concepts that form the foundation for advanced analytics.

Learn what DAX (Data Analysis Expressions) is, why it’s essential in Power BI, and how its syntax works for creating measures, calculated columns, and advanced data analysis.

Learn the structure of DAX syntax and discover the four main areas where DAX calculations are applied: Measures, Calculated Columns, Calculated Tables, and Row-Level Security.

Understand why functions are essential in DAX, how they simplify calculations, and the role they play in performing dynamic data analysis in Power BI.

Learn the two key DAX concepts — Row Context and Filter Context, and understand how they influence calculations in Power BI, including implicit and explicit contexts.

Practice applying Row Context and Filter Context in Power BI by creating calculated columns, measures, and visuals using the datasets as attached.

Hint: Create a new Power BI Solution File to complete this Exercise!

Learn what DAX measures are, how they provide dynamic calculations based on context, and why they are essential for reporting in Power BI.

Understand the difference between Implicit Measures created automatically in Power BI and Explicit Measures defined using DAX, along with their benefits and limitations.

Learn what Calculated Columns are in Power BI, how they use DAX formulas to add new columns to tables, and why they follow row context for data calculations.

Learn how to create Calculated Tables in Power BI using DAX, understand their role in modeling, and explore scenarios where they enhance flexibility and performance.

Learn how Row-Level Security (RLS) in Power BI restricts data access for specific users by applying filters at the row level, including static and dynamic RLS options.

Compare Measures and Calculated Columns in Power BI, understand their differences in context, storage, and usage, and learn when to use each for reporting.

Practice creating a dedicated DAX Measures Table in Power BI to keep your data model organized and manage explicit measures efficiently.

Hint: Continue on your Adventure Works Lecture Project File to complete this Exercise.

Explore the four types of operators in DAX: arithmetic, comparison, text concatenation, and logical, and learn how they are used in calculations and expressions.

Learn how logical functions in DAX evaluate expressions, return TRUE/FALSE values, and apply conditional logic to create dynamic measures and calculated columns.

Learn how to use the AND( ) function and the equivalent '&&' operator in DAX to test multiple conditions and return TRUE only when all conditions are met.

Practice using the AND() function and '&&' operator in Power BI by testing multiple conditions on the Orders table. Use the dataset file Practice_DAX_SuperStoreUS-2015.xlsx to complete this exercise.

Hint: Create a new Power BI Solution File to complete this Exercise!

Learn how to use the OR( ) function and the equivalent '||' operator in DAX to return TRUE when at least one condition is met in your Power BI calculations.

Practice using the OR( ) function and '||' operator in Power BI to test multiple conditions on the Orders table.

Use the dataset file Practice_DAX_SuperStoreUS-2015.xlsx to complete this exercise.

Learn how to use the IF( ) function in DAX to apply conditional logic, returning different results based on whether a condition is TRUE or FALSE in Power BI.

Practice using the IF( ) function in Power BI to create a calculated column that categorizes sales values into 'Very High', 'Moderate', or 'Low' based on conditions.

Use the dataset file Practice_DAX_SuperStoreUS-2015.xlsx for this exercise.

In this lecture, you will learn how to use the NOT( ) logical function in DAX to reverse Boolean values and create conditional logic in Power BI.

In this practice lecture, you will learn how to combine the NOT( ) and IF( ) functions in DAX to create conditional logic.

You will build a calculated column that classifies orders as ‘Normal Orders’ or ‘Outlier Rare Orders’ based on sales values.

Use the dataset file Practice_DAX_SuperStoreUS-2015.xlsx for this exercise.

In this lecture, you will learn how to use the SWITCH( ) function in DAX to simplify multiple conditional statements. SWITCH helps return different results based on matching values, making it a cleaner alternative to nested IF statements.

In this practice session, you will apply the SWITCH( ) function in DAX to create a calculated column named ‘Stock Level Check.’

This exercise compares Reorder Point and Safety Stock Level to classify products into categories such as Good, At Risk, Minimal, or Not Set.

Hint: Duplicate your current Adventure Works Power BI Solution File and use it to complete this practice.

In this lecture, you will learn how to use ISBLANK( ) and IFERROR( ) functions in DAX for data validation and error handling.

ISBLANK( ) helps identify missing or null values, while IFERROR( ) ensures smooth calculations by replacing errors with custom values. These functions can be applied in both calculated columns and measures.

In this lecture, you will learn how to use the ISBLANK( ) and IFERROR( ) functions in DAX for effective data validation and error handling. We will create calculated columns to handle missing values and prevent division errors in your Power BI models.

By the end of this session, you will be able to:

• Apply ISBLANK( ) to replace blank values with a default value.

• Use IFERROR( ) to manage calculation errors, such as divide-by-zero scenarios.

• Improve data quality and ensure robust calculations in your reports.

In this lecture, you will get an introduction to three important categories of DAX functions: Aggregation, Math, and Statistical functions. These functions help you perform calculations such as summing values, rounding numbers, and analyzing data distributions.

In this lecture, you will learn how to use Aggregation Functions in DAX, including SUM( ), MAX( ), MIN( ), DIVIDE( ), AVERAGE( ), and PRODUCT( ). These functions are essential for summarizing data and creating dynamic calculations in Power BI.

By the end of this session, you will be able to:

• Understand how aggregation functions work to return a single value from a set of data.

• Apply these functions in DAX Measures to create summaries that respond to filters and slicers.

• Combine aggregation functions with other DAX functions for advanced calculations, such as average sales per region or total sales by category.

In this practice lecture, you will apply Aggregation Functions in DAX to solve real-world business questions using the SuperStore dataset. This hands-on exercise will help you reinforce your understanding of functions like SUM( ), MAX( ), MIN( ), DIVIDE( ), and AVERAGE( ).

By the end of this session, you will be able to:

• Create a DAX Measure to calculate the Total Sales using SUM( ).

• Find the Minimum and Maximum Shipping Costs using MIN( ) and MAX( ).

• Use the DIVIDE( ) function to calculate Average Profit while handling division errors.

• Compute the Average Profit using the AVERAGE( ) function.

In this practice lecture, you will learn how to use the PRODUCT( ) function in DAX to calculate compound growth rates over multiple periods. Using the provided dataset, you will apply this function to compute the Compound Growth Rate for a fictitious company over a 20-year period.

This lecture covers key Math Functions in DAX such as CEILING( ), FLOOR( ), INT( ), and ROUND( ). You will learn how to use these functions to manipulate numerical data, clean and prepare data for analysis, and ensure values are in the desired format for reporting.

In this practice lecture, you will apply Math Functions in DAX, including CEILING( ), FLOOR( ), INT( ), and ROUND( ), to real-world financial data.

You will create calculated columns to round values up or down, extract integer parts, and format numbers for reporting. This exercise will help you understand the impact of rounding and formatting in financial analysis.

This lecture introduces key Statistical Functions in DAX, including STDEV.P( ), STDEV.S( ), RANKX( ), and MEDIAN( ).

You will learn how to calculate measures related to data variability and distribution, such as standard deviation and ranking, and apply these functions to create deeper insights in Power BI reports.

In this practice lecture, you will apply Statistical Functions in DAX, including STDEV.P( ), STDEV.S( ), RANKX( ), and MEDIAN( ), to real-world financial data.

You will create measures to calculate population and sample standard deviation, rank products by sales, and compute median profit. This exercise will help you analyze data variability and distribution effectively in Power BI.

This lecture introduces Text Functions in DAX, which are essential for manipulating and analyzing text data in Power BI.

You will learn how to clean, format, and extract meaningful information using functions like LEFT( ), RIGHT( ), MID( ), SEARCH( ), LEN( ), CONCATENATE( ), FORMAT( ), and more.

These functions are widely used for data cleaning, transformation, and analysis.

This lecture explains how to use LEFT( ), MID( ), and RIGHT( ) functions in DAX to extract specific characters from text strings. These functions are essential for cleaning and transforming text data in Power BI, enabling you to isolate portions of text for analysis or reporting.

In this practice lecture, you will use LEFT( ), MID( ), and RIGHT( ) functions in DAX to extract specific characters from text columns in Power BI.

You will create calculated columns to abbreviate month names and extract year values from dates, improving data readability and enabling better grouping for analysis.

This lecture demonstrates how to combine SEARCH( ), RIGHT( ), and LEN( ) functions in DAX to extract values after a specific delimiter in text strings.

You will learn how to locate a substring position, calculate string length, and retrieve the required portion of text for data cleaning and transformation in Power BI.

In this practice lecture, you will use SEARCH( ), RIGHT( ), and LEN( ) functions in DAX to extract product size values from SKU codes in the Adventure Works dataset.

You will create a calculated column that isolates values after a specific delimiter, helping you clean and structure product data for reporting.

Hint: Duplicate your Adventure Works Lecture Project File and use it to practice this Exercise!

This lecture explains how to use COMBINEVALUES( ) and CONCATENATE( ) functions in DAX to join multiple text values into a single string. You will learn how to combine columns for creating unique identifiers or formatted text fields in Power BI.

In this practice lecture, you will apply COMBINEVALUES( ) and CONCATENATE( ) functions in DAX to join multiple text fields into a single string. This exercise will help you create combined identifiers or formatted text for better data presentation in Power BI.

This lecture explains how to use the FORMAT( ) function in DAX to convert values into text with a specified format.

You will learn how to format dates, times, and numbers for better readability and presentation in Power BI reports.

In this practice lecture, you will apply the FORMAT( ) function in DAX across three real-world scenarios:

1. Formatting dates in a sales report for better readability.

2. Displaying time values in an employee timesheet in AM/PM format.

3. Applying locale-specific date formatting for a financial report.

This lecture explains how to use the UNICHAR( ) function in DAX to generate Unicode characters based on numeric codes. You will learn how to add symbols, icons, and special characters to your Power BI reports for improved visualization and formatting.

In this practice lecture, you will use the UNICHAR( ) function in DAX to generate Unicode characters and symbols for Power BI reports. You will create dynamic visuals by adding icons such as arrows, checkmarks, and other special characters to enhance data presentation.

This lecture explains how to use the TRIM( ) function in DAX to remove leading and trailing spaces from text values. You will learn how this function helps clean and standardize text data for accurate analysis in Power BI.

This lecture explains how to use the REPLACE( ) function in DAX to substitute part of a text string with new text. You will learn how to clean and modify text values efficiently for better data preparation in Power BI.

This lecture explains how to use UPPER( ) and LOWER( ) functions in DAX to convert text to uppercase or lowercase.

You will learn how to standardize text formatting for consistency in Power BI reports.

In this practice lecture, you will apply TRIM( ), REPLACE( ), UPPER( ), and LOWER( ) functions in DAX to clean and format text data. You will remove extra spaces, replace placeholder text, and standardize case formatting for names and cities in Power BI.

This section introduces advanced DAX calculations in Power BI, focusing on Date & Time functions, filter modifiers, and Time Intelligence functions. By the end of this section, students will be able to create dynamic calculations using functions like CALCULATE( ), FILTER( ), and various date functions to build powerful analytical models.

In this lecture, you will learn the fundamentals of Date and Time functions in DAX. These functions are essential for creating dynamic calculations based on dates, such as building calendar tables, performing time-based analysis, and enabling Time Intelligence features in Power BI.

By the end of this lecture, you will understand the role of Date and Time functions and how they support advanced reporting.

In this lecture, you will learn how to create a Calendar Table in Power BI using the CALENDAR( ) and DATE( ) functions. A Calendar Table is essential for enabling Time Intelligence calculations and performing accurate date-based analysis.

In this hands-on lecture, you will practice creating Calendar Tables using CALENDAR( ) and DATE( ) functions in Power BI. We will cover two practical scenarios:

1. Building a simple Calendar Table for Time Intelligence analysis.

2. Enhancing the Calendar Table with additional columns like Year, Month, Month Name, and Quarter using ADDCOLUMNS( ).

In this lecture, you will learn how to use the EOMONTH( ) function in DAX to calculate the end of a month for a given date. This function is useful for creating month-end reports, financial statements, and time-based calculations.

By the end of this lecture, you will be able to apply EOMONTH( ) in practical scenarios to enhance your date-based analysis in Power BI.

In this practical session, you will apply the EOMONTH( ) function in two real-world scenarios:

1. Monthly Sales Report – Calculate total sales at the end of each month using a sales dataset.

2. Subscription Billing – Determine billing end dates by adding months to a start date for subscription plans.

In this lecture, you will learn how to use the DAY( ), MONTH( ), and YEAR( ) functions in DAX to extract specific components from a date.

These functions are essential for creating custom date-based calculations and building dynamic columns for reporting. By the end of this lecture, you will be able to apply these functions to enhance your Power BI data models.

In this lecture, you will learn how to use the HOUR( ), MINUTE( ), and SECOND( ) functions in DAX to extract time components from a DateTime Value. These functions are useful for creating time-based calculations, analyzing trends by hour or minute, and building detailed time intelligence reports

In this lecture, you will learn how to use the TODAY( ) and NOW( ) functions in DAX to return the current date and time dynamically.

These functions are essential for creating real-time calculations, aging analysis, and time-sensitive reports.

In this lecture, you will learn how to use the WEEKDAY( ) and WEEKNUM( ) functions in DAX to identify the day of the week and the week number for any given date. These functions are essential for creating weekly reports, analyzing trends by weekday, and building time-based groupings.

In this practice session, you will apply 10 commonly used Date and Time functions in DAX, including DAY( ), MONTH( ), YEAR( ), HOUR( ), MINUTE( ), SECOND( ), TODAY( ), NOW( ), WEEKDAY( ), and WEEKNUM( ).

Through hands-on exercises, you will learn how to extract and manipulate date and time components for dynamic reporting.

In this lecture, you will learn how to use the DATEDIFF( ) function in DAX to calculate the difference between two dates across various intervals such as days, months, and years.

This function is essential for creating age calculations, subscription durations, and time-based metrics.

In this practice session, you will apply the DATEDIFF( ) function to calculate the difference between two dates using the “SuperStoreUS-2015.xlsx” dataset.

You will create a new column called Preparation Time to measure the interval between Order Date and Ship Date in days.

By the end of this lecture, you will be able to use DATEDIFF( ) for real-world scenarios such as delivery time analysis and performance tracking in Power BI.

In this lecture, you will learn the most important and powerful DAX function: CALCULATE( ). This function allows you to modify filter context and perform dynamic calculations in Power BI.

You will explore how CALCULATE( ) works with filter modifiers and understand its role in advanced analytics.

By the end of this lecture, you will be able to use CALCULATE( ) to create complex measures and unlock advanced reporting capabilities.

In this lecture, you will learn how to use Boolean Filter Expressions within the CALCULATE( ) function in DAX.

Boolean filters allow you to apply conditions that evaluate to TRUE or FALSE, enabling precise control over filter context.

You will also explore key rules for using Boolean filters, including logical operators like AND (&&) and OR (||).

In this lecture, you will learn how to use Filter Modifier Functions within the CALCULATE( ) function to gain advanced control over filter context in DAX. These functions allow you to override, remove, or keep filters dynamically, enabling more flexible calculations.

You will explore key functions such as ALL( ), ALLEXCEPT( ), ALLSELECTED( ), REMOVEFILTERS( ), KEEPFILTERS( ), and USERELATIONSHIP( ).

By the end of this lecture, you will be able to apply these modifiers to create powerful and dynamic measures in Power BI.

In this lecture, you will learn how to use the ALL( ) function in DAX to remove filters from columns or tables. This function is essential for performing calculations on the entire dataset, ignoring any applied filters.

You will explore its syntax, purpose, and practical use cases such as creating measures that calculate totals regardless of slicer selections.

By the end of this lecture, you will be able to apply ALL( ) effectively to control filter context in Power BI.

In this lecture, you will learn how to combine CALCULATE( ) with the ALL( ) function to override filters in DAX measures.

You will explore practical scenarios such as calculating total sales while ignoring filters applied to specific columns (e.g., Region) or the entire table.

By the end of this lecture, you will be able to create measures that ignore filter context, enabling accurate comparisons and advanced analytics in Power BI.

In this lecture, you will learn how to use the ALLEXCEPT( ) function in DAX to remove all filters from a table except for the specified columns.

This function is useful for creating calculations that respect certain filters while ignoring others, such as calculating totals by category while disregarding other slicers.

By the end of this lecture, you will understand the syntax, purpose, and practical applications of ALLEXCEPT( ) in Power BI.

In this lecture, you will learn how to combine CALCULATE( ) with ALLEXCEPT( ) to create advanced measures that ignore certain filters while keeping others. You will explore practical scenarios such as:

• Calculating Total Sales by Product Category while ignoring filters on other columns like Region or Customer Segment.

• Computing Percentage of Sales Contribution by Sub-Category relative to its parent Product Category.

By the end of this lecture, you will be able to apply CALCULATE( ) and ALLEXCEPT( ) together to build dynamic and context-aware calculations in Power BI.

In this lecture, you will learn how to use the ALLSELECTED( ) function in DAX to return all rows or values while respecting the user’s current selections from slicers or visuals.

This function is useful for creating dynamic calculations that adapt to user interactions, such as calculating percentages or totals based on selected context.

You will also explore practical use cases like overriding certain filters while keeping external selections.

By the end of this lecture, you will be able to apply ALLSELECTED( ) to build interactive and flexible Power BI reports.

In this lecture, you will learn how to combine CALCULATE( ) with ALLSELECTED( ) to create dynamic measures that respect user selections while overriding other filters. You will explore practical scenarios such as:

• Calculating Total Sales for Selected Regions based on slicer selections.

• Computing Percentage of Total Sales for each product category while considering selected regions.

By the end of this lecture, you will be able to apply CALCULATE( ) and ALLSELECTED( ) together to build interactive, context-aware calculations for advanced Power BI reports.

In this lecture, you will learn how to use the REMOVEFILTERS( ) function in DAX to clear filters from specified tables or columns. This function is useful for creating calculations that ignore certain filters while maintaining others, especially when used inside CALCULATE( ).

By the end of this lecture, you will understand how REMOVEFILTERS( ) modifies filter context and how to apply it in practical Power BI scenarios.

In this lecture, you will learn how to combine CALCULATE( ) with REMOVEFILTERS( ) to clear filters from specific columns or tables in DAX. You will explore practical scenarios such as:

• Calculating Total Sales while ignoring filters on Product Category.

• Computing Total Profit while removing filters on Order Date and Ship Date.

By the end of this lecture, you will understand how to use REMOVEFILTERS( ) inside CALCULATE( ) to override filter context and create accurate, unrestricted calculations in Power BI.

In this lecture, you will learn how to use the KEEPFILTERS( ) function in DAX to preserve existing filter context when adding new filters in a calculation.

Normally, CALCULATE( ) replaces existing filters with new ones, but KEEPFILTERS( ) changes this behavior by combining both.

You will explore its syntax, purpose, and practical use cases for creating more precise and controlled calculations. By the end of this lecture, you will understand how to apply KEEPFILTERS( ) effectively in Power BI.

In this lecture, you will learn how to combine CALCULATE( ) with KEEPFILTERS( ) to preserve existing filters while adding new ones in DAX.

You will explore practical scenarios such as:

• Calculating Total Sales for Technology Category within an existing date filter context.

• Applying multiple filters simultaneously (e.g., Region and Product Category) without overriding existing filters.

By the end of this lecture, you will understand how to use KEEPFILTERS( ) to create precise, context-aware calculations for complex Power BI reports.

In this lecture, you will learn how to use Table Filter Expressions within the CALCULATE( ) function in DAX.

Unlike Boolean filters, table filter expressions allow you to apply complex conditions using functions like FILTER( ), enabling advanced scenarios such as referencing multiple columns, measures, or nested calculations.

By the end of this lecture, you will understand when and why to use table filter expressions and how they overcome the limitations of Boolean filters in Power BI.

In this lecture, you will learn the role of the FILTER( ) function in DAX and how it is used to return a subset of rows from a table based on specified conditions.

By the end of this session, you will be able to:

• Understand the syntax of the FILTER function: FILTER(<table>, <filter_condition>).

• Apply FILTER to iterate over rows and evaluate Boolean expressions.

• Use FILTER to create complex filter conditions that cannot be defined by simple Boolean expressions.

• Combine FILTER with other DAX functions for advanced calculations.

In this lecture, you will learn how to combine CALCULATE( ) and FILTER( ) functions in DAX to perform advanced filtering scenarios that go beyond simple Boolean conditions.

By the end of this session, you will be able to:

• Understand why FILTER( ) is essential for complex filtering logic in DAX.

• Apply CALCULATE( ) + FILTER( ) to:

  • Filter based on measures or dynamic conditions (e.g., high-value customers).

  • Perform cumulative calculations using row context and iteration.

  • Implement dynamic filtering based on related tables using the RELATED() function.

• Create advanced DAX measures such as:

  • HighValueCustomerSales for customers with purchases above $20,000.

  • CumulativeSales up to the current date for each row.

  • Total_Returned_Qty for orders with a “Returned” status.

This lecture includes practical exercises using the SuperStore_US Sales Dataset and a Power BI solution file for hands-on practice.

In this lecture, you will learn the fundamentals of Time Intelligence functions in DAX and why they are essential for creating dynamic and insightful Power BI reports.

By the end of this session, you will be able to:

• Understand the role of Time Intelligence functions for analyzing trends, forecasting, and comparing performance across time periods.

• Explore commonly used Time Intelligence functions such as:

  • TOTALYTD( ), DATESYTD( ), SAMEPERIODLASTYEAR( ), PARALLELPERIOD( ), DATEADD( )

  • Functions for Year-over-Year Analysis, Month-to-Date and Year-to-Date calculations, and Rolling Averages.

• Learn the requirements for a dedicated Date Table to ensure Time Intelligence functions work correctly:

  • Continuous date range covering all dates in your data model.

  • Unique dates with no duplicates.

  • Marking the table as a Date Table in Power BI.

  • Adding additional columns for Year, Quarter, Month, Day, etc.

• Create a Date Table using DAX (CALENDAR + ADDCOLUMNS) or Power Query M code template.

Learn how to use the DATEADD( ) function in DAX to shift dates forward or backward by a specified interval. This function is essential for comparing data across different time periods, such as month-over-month or year-over-year analysis.

Learn how to combine DATEADD( ) with CALCULATE( ) in DAX to perform advanced time-based comparisons.

This lecture covers practical scenarios like Year-over-Year (YoY) and Month-over-Month (MoM) sales analysis using Power BI, along with creating a dedicated Calendar table for accurate Time Intelligence calculations.

Learn how to use DATESYTD( ), DATESQTD( ), and DATESMTD( ) in DAX to return date ranges for Year-to-Date, Quarter-to-Date, and Month-to-Date calculations. These functions are essential for building cumulative time-based measures in Power BI.

Learn how to combine CALCULATE( ) with DATESYTD( ), DATESQTD( ), and DATESMTD( ) to track cumulative performance over Year-to-Date, Quarter-to-Date, and Month-to-Date periods. This lecture demonstrates practical DAX measures for analyzing sales trends in Power BI.

Learn how to use PARALLELPERIOD( ) in DAX to compare data across parallel time periods like previous year, quarter, or month.

Unlike DATEADD( ), which offers flexible date shifting for custom intervals, PARALLELPERIOD( ) is designed specifically for standard time comparisons, making it simpler for Year-over-Year, Quarter-over-Quarter, and Month-over-Month analysis.

Learn how to combine CALCULATE( ) with PARALLELPERIOD( ) in DAX to compare current period performance with previous periods, such as last month or last year. This lecture demonstrates practical use cases for trend analysis and explains how this approach differs from using DATEADD( ).

Learn how to use SAMEPERIODLASTYEAR( ) in DAX to compare data from the current period with the same period in the previous year. This function is ideal for Year-over-Year analysis, helping you track trends and performance across years.

Learn how to combine CALCULATE( ) with SAMEPERIODLASTYEAR( ) in DAX to perform Year-over-Year comparisons. This lecture demonstrates how to create measures that compare current year performance with the same period last year, providing insights into trends and growth in Power BI.

Learn how to use DATESINPERIOD( ) in DAX to return a range of dates starting from a specific date and continuing for a defined number of intervals.

This function is ideal for creating custom time periods, moving averages, and year-to-date calculations in Power BI.

Learn how to combine CALCULATE( ) with DATESINPERIOD( ) in DAX to create rolling time-based calculations such as Rolling 12-Month Sales, Rolling 90-Day Sales, and custom Year-to-Date periods. This lecture demonstrates practical scenarios for trend analysis and moving averages in Power BI.

Learn how to use DATESBETWEEN( ) in DAX to return all dates between two specified dates. This function is useful for defining custom date ranges for time-based calculations in Power BI, such as filtering data for specific periods.

Learn how to combine CALCULATE( ) with DATESBETWEEN( ) in DAX to calculate values within custom date ranges. This lecture covers practical scenarios such as sales during a promotional period, year-to-date sales up to a specific date, and other custom time-based calculations in Power BI.

In this section, we dive deeper into creating advanced calculations using DAX in Power BI. You’ll learn how iterator functions work, explore aggregation and counting functions, and understand how to apply them effectively in your reports. By the end, you’ll be able to use functions like SUMX, COUNTX, RANKX, and more to build dynamic, insightful measures.

In this lecture, you will learn the concept of iterator functions in DAX and how they differ from standard aggregation functions. We’ll explore why iterator functions are powerful for performing row-by-row calculations and aggregating results, and when to use them for complex or conditional aggregations.

In this lecture, you will learn how to use iterator aggregation functions in DAX, such as SUMX, COUNTX, PRODUCTX, and MAXX. These functions allow you to perform row-by-row calculations before aggregating results, making them ideal for complex scenarios.

We’ll cover practical examples like calculating total sales and applying filters for conditional aggregations. By the end, you’ll be able to create dynamic measures using iterator functions for advanced data analysis.

In this lecture, you will learn the fundamentals of counting functions in DAX and how they help summarize data, identify trends, and validate data quality. We’ll explore functions like COUNT, COUNTA, COUNTBLANK, DISTINCTCOUNT, and COUNTROWS, along with their practical use cases in reporting and dashboards.

By the end, you’ll understand how to apply these functions to measure data volume, uniqueness, and completeness effectively.

In this lecture, you will learn how to use basic counting functions in DAX, including COUNT( ), COUNTA( ), and COUNTROWS( ). These functions help you count numeric values, non-blank entries, and rows in a table.

We’ll cover practical examples such as counting sales transactions, product listings, and total orders.

In this lecture, you will learn how to count unique values in your dataset using DISTINCTCOUNT( ) and DISTINCTCOUNTNOBLANK( ). These functions help identify distinct entries, such as unique customers, products, or responses, and handle scenarios where blanks need to be excluded.

In this lecture, you will learn how to use the COUNTX( ) iterator function in DAX to perform conditional counting across rows. We’ll explore scenarios such as counting orders with discounts and identifying high-value orders above a threshold.

By the end, you’ll understand how COUNTX( ) works with expressions and conditions, enabling you to create dynamic measures for advanced reporting.

In this lecture, you will learn how to use the RANKX( ) iterator function in DAX to rank values dynamically within a table. We’ll cover its syntax, optional parameters for sorting and handling ties, and practical scenarios such as ranking products by total sales.

By the end, you’ll be able to create ranking measures that enhance dashboards and help identify top-performing items for better decision-making.

In this section, we focus on advanced DAX techniques for managing relationships in Power BI. You’ll learn how to use functions like RELATED( ), RELATEDTABLE( ), and USERELATIONSHIP( ) to retrieve data across tables and activate inactive relationships.

In this lecture, you will learn the importance of relationship management in Power BI and how it impacts data integrity, complex calculations, dynamic reporting, and performance optimization.

We’ll also introduce key DAX functions for managing relationships, such as RELATED( ), RELATEDTABLE( ), USERELATIONSHIP( ), and CROSSFILTER( ).

By the end, you’ll understand why relationships are critical for building accurate and efficient data models.

In this lecture, you will learn how to use the RELATED( ) and RELATEDTABLE( ) functions in DAX to retrieve values from related tables and perform aggregations across relationships. We’ll cover practical examples such as fetching product names for sales, calculating total sales amounts, counting related rows, and summing related values.

By the end, you’ll be able to apply these functions to create dynamic calculated columns and measures that enhance your data model.

Hint: Duplicate your Adventure Works Lecture Project File, save as "Section7_AWWorks.pbix", and use it to practice this Exercise.

In this Exercise, you will practice using the RELATED( ) and RELATEDTABLE( ) functions with a real-world dataset, Global Retail Insights. You’ll create calculated columns to retrieve customer names from the Customers table and count orders for each customer using COUNTROWS( ) with RELATEDTABLE( ).

In this lecture, you will learn how to use the USERELATIONSHIP( ) function in DAX to activate inactive relationships for specific calculations. We’ll explore practical scenarios such as comparing sales by different dates, dynamic date filtering, activating inactive relationships, and performing year-over-year comparisons using multiple date columns.

In this lecture, you will learn how to combine CALCULATE( ) with USERELATIONSHIP( ) to activate inactive relationships for specific calculations.

Using the Adventure Works dataset, we’ll create measures that calculate total revenue based on different date columns, such as OrderDate and ShipDate.

By the end, you’ll understand how to handle multiple relationships in your data model and apply this technique for advanced reporting scenarios.

In this lecture, we introduce Section 8, where we transition from theory to practice by applying all the DAX concepts learned in previous sections to a real-world scenario using the Adventure Works dataset. This section is designed as a hands-on project to reinforce your understanding and build confidence in using DAX effectively.

You will continue working with the Adventure Works Lecture Project file that was prepared during earlier sections. Before diving into exercises, we’ll review important checklists for your data model, including verifying relationships between tables, ensuring key fields are properly connected, hiding unnecessary columns, and optimizing the model for performance. A clean and well-structured model is essential for clear insights.

Additionally, we’ll introduce naming conventions for DAX exercises to maintain clarity and consistency throughout the course:

• Calculated Columns → CC01, CC02, CC03

• Measures → M01, M02, M03

• Calculated Tables → CT01, CT02, CT03

• What-If Parameters → WP01, WP02

• Visual Interpretation Questions → VQ01, VQ02

By the end of this lecture, you’ll be ready to apply DAX in a structured, professional way and prepare for advanced exercises that simulate real business scenarios.

Before starting DAX exercises, ensure your Adventure Works data model is clean and optimized. In this lecture, you’ll review relationships, confirm key fields, hide unnecessary columns, and tidy up the model for better performance. A well-prepared model ensures accurate calculations and clear insights.

In this lecture, we outline the naming conventions for all DAX exercises in this section.

You’ll learn how calculated columns, measures, calculated tables, What-If parameters, and visual interpretation questions are labeled for easy reference throughout the course.

This ensures clarity and consistency when working on exercises and reviewing solutions.

In this lecture, you’ll create a calculated column called Weekend in the Date table using DAX. This column will return “Yes” for Saturdays and Sundays, and “No” for weekdays.

You’ll learn how to use the IF( ) function with logical operators to implement this condition, making your reports more flexible for weekend vs. weekday analysis.

In this lecture, you’ll create an End of Month calculated column in the Date table using the EOMONTH( ) function. This column identifies the last date of the month for each date, which is useful for month-end reporting and grouping data by closing periods.

You’ll learn the logic behind the function and apply it in your model for better time-based analysis.

In this lecture, you’ll prepare your data model for Time Intelligence functions in Power BI. We’ll review why a dedicated Date table and proper Date/DateTime relationships are essential.

You’ll learn how to convert surrogate keys into actual date columns in the Sales table, ensuring your model is ready for advanced time-based calculations like year-to-date and month-to-date analysis.

In this lecture, you’ll convert surrogate keys in the Sales table into proper Date columns for Order Date, Ship Date, and Due Date using DAX.

You’ll learn how to use the DATE( ) function along with LEFT( ), MID( ), and RIGHT( ) to extract year, month, and day from keys and format them as Date data types. This step is essential for enabling Time Intelligence functions in Power BI.

In this lecture, you’ll update the data model relationships to support Time Intelligence functions.

You’ll remove old relationships based on surrogate keys and create a new active relationship for Order Date, along with two inactive relationships for Ship Date and Due Date.

These changes ensure accurate time-based calculations and allow flexibility using USERELATIONSHIP( ) in DAX.

In this lecture, you’ll create a calculated column to check for missing product information using the ISBLANK( ) function in DAX. If the Product field is blank, the column will return “PRODUCT INFO MISSING”; otherwise, it will return “PRODUCT INFO ALL OK.” This helps ensure data quality and reliability in your reports.

In this lecture, you’ll create a calculated column to evaluate stock levels using SWITCH( ) and ISBLANK( ) in DAX.

The logic compares Reorder Point and Safety Stock Level to categorize inventory status as “Good,” “At Risk,” “Minimal,” or “Incomplete.”

This helps identify potential stock issues and supports better inventory management decisions.

In this lecture, you’ll create a calculated column to extract product size from the SKU using DAX text functions.

You’ll use SEARCH( ), RIGHT( ), and LEN( ) to locate the second hyphen in the SKU and return the size value.

A fallback value “N/A” will be applied if the size cannot be found. This improves product categorization and filtering in reports.

In this lecture, you will learn why creating a dedicated Measures table is considered a best practice in Power BI.

We’ll walk through the steps to create a new table named DAX_Measures using the Enter Data option.

By the end of this session, you’ll understand how this table acts as a container for all your measures, keeping your data model clean, organized, and easy to maintain. This approach helps prevent clutter and improves efficiency when working on large projects.

In this lecture, we will review the Adventure Works data model, which serves as the foundation for creating accurate and efficient DAX measures.

You’ll learn how Fact Tables and Dimension Tables are structured, and how key columns like SalesOrderLineKey and Sales Order enable analysis at both line-item and order levels.

By the end of this session, you’ll have a solid understanding of the schema, empowering you to write better DAX, build smarter visuals, and interpret business insights with confidence.

In this lecture, you will learn how to create a DAX measure to calculate the total quantity of products sold across all orders using the SUM function. We’ll walk through the steps to aggregate the Order Quantity column and apply this measure to a KPI card and bar charts for visual analysis.

In this lecture, you will learn how to create a DAX measure that counts the number of unique sales orders using the DISTINCTCOUNT function.

We’ll demonstrate how to apply this measure to a KPI card and a matrix visual to analyze order activity by region.

In this lecture, you will learn how to create a DAX measure that calculates the number of sales orders placed on weekends using the CALCULATE function with a Boolean filter.

We’ll walk through modifying the filter context to include only rows where CC01_Weekend = "Yes" and then visualize the results using a donut chart and a line chart to analyze weekend trends.

By the end of this session, you’ll be able to evaluate weekend performance and campaign effectiveness with confidence.

In this lecture, you will learn how to calculate the percentage of weekend sales orders using the DIVIDE function in DAX for safe ratio analysis. We’ll reference previously created measures and format the result as a percentage for clear interpretation.

Finally, we’ll visualize the percentage contribution of weekend orders using KPI cards and trend charts.

By the end of this session, you’ll be able to identify weekend performance and its impact on overall sales.

In this lecture, you will learn how to calculate Total Revenue using DAX with the SUMX function for row-by-row calculations.

We’ll combine Unit Price and Order Quantity from the Sales Fact Table and apply discounts using (1 - Unit Price Discount Pct) for accurate revenue computation.

Finally, we’ll visualize revenue insights using KPI cards and TreeMaps to highlight top-performing categories and products.

By the end of this session, you’ll be able to create dynamic revenue measures that reflect true sales performance.

In this lecture, you will learn how to calculate Total Cost using DAX with the SUMX function for row-wise multiplication and the RELATED function to access product costs from a related table.

We’ll combine Order Quantity from the Sales Fact Table with Standard Cost from the Product table to compute the total cost of goods sold.

Finally, we’ll visualize cost vs. revenue using combo charts to assess profitability and identify high-cost products.

In this lecture, you will learn how to calculate Total Profit by subtracting Total Cost from Total Revenue using previously created measures in DAX.

This simple yet powerful calculation helps you assess overall profitability.

We’ll also visualize profit vs. revenue using a scatter chart to identify high-profit outliers and top-performing segments.

In this lecture, you will learn how to calculate Profit Margin % using DAX with the DIVIDE function for safe ratio calculations.

We’ll reference previously created profit and revenue measures and format the result as a percentage for clear interpretation.

Finally, we’ll visualize margin trends over time using a line chart to identify periods of strong or weak profitability.

In this lecture, you will learn how to calculate Year-to-Date (YTD) Revenue using DAX with the DATESYTD function for time-based analysis.

We’ll use CALCULATE to modify the time context and apply cumulative logic that resets at the start of each year. Finally, we’ll visualize YTD revenue trends using an area chart to identify growth patterns and seasonal performance.

In this lecture, you will learn how to calculate Previous Month Revenue using DAX with the DATEADD function to shift the date context by one month. We’ll use CALCULATE to modify the filter context and dynamically compute last month’s revenue, even when slicing by year or quarter.

Finally, we’ll visualize current vs. previous month revenue using a clustered column chart to identify trends and compare performance.

In this lecture, you will learn how to calculate Cumulative Revenue for the Last 90 Days using DAX with the DATESINPERIOD function.

We’ll use CALCULATE to apply a rolling 90-day filter anchored to the latest visible date in the current context.

Finally, we’ll visualize cumulative trends alongside monthly revenue using area charts to uncover seasonal patterns, promotion effects, and short-term performance shifts.

In this lecture, you will learn how to create a dynamic Monthly Revenue Target measure using DAX.

The target is calculated by applying a 5% growth to the same month’s revenue from the previous year.

If historical data for that month is missing, the measure falls back to last month’s revenue with the same 5% uplift.

We’ll use functions like SAMEPERIODLASTYEAR, DATEADD, and conditional logic with IF to implement this approach.

Finally, we’ll visualize targets alongside actual revenue using KPI cards and tables for performance tracking

In this lecture, you will learn how to calculate the total number of unique End User Customers using DAX while excluding reseller rows.

We’ll use DISTINCTCOUNT to count unique customer keys and apply a filter to remove system-generated entries (CustomerKey = -1) that represent reseller transactions.

Finally, we’ll visualize the customer count by country using a column chart to assess customer base size and reach.

In this lecture, you will learn how to calculate the total number of unique products in the Product table using DAX.

We’ll use the DISTINCTCOUNT function on the ProductKey column to count unique product entries.

Finally, we’ll visualize product diversity by category using a donut chart, helping assess catalog size and distribution.

In this lecture, you will learn how to create Last Month Metrics using DAX for key business indicators such as Sales Orders, Revenue, Profit, Profit Margin %, and Quantity Sold.

We’ll use CALCULATE combined with DATEADD to shift the date context by one month, ensuring dynamic calculations regardless of current filters.

Finally, we’ll visualize these metrics in tables and KPI cards to monitor short-term performance trends.

In this lecture, you will learn how to calculate Total Profit by Product Category using DAX while ignoring all other filters except the category filter.

We’ll use CALCULATE combined with ALLEXCEPT to retain only the category context and remove other filters like product name or subcategory.

Additionally, you’ll create a second measure to calculate each product’s profit as a percentage of its category total using the DIVIDE function.

Finally, we’ll visualize these measures in a matrix to analyze category-level profitability and product contribution.

In this lecture, you will learn how to calculate Total Revenue while ignoring filters applied to the Sales Territory table using DAX.

We’ll use CALCULATE combined with REMOVEFILTERS to remove region-level filter context and return unbiased revenue results.

Finally, we’ll visualize the difference between filtered and unfiltered revenue in a table to understand the impact of geographic filters.

In this lecture, you will learn how to calculate the total number of unique Resellers using DAX while excluding system-generated rows that represent end-user customers (ResellerKey = -1).

We’ll use DISTINCTCOUNT to count unique reseller keys and apply a filter inside CALCULATE to remove invalid entries.

Finally, we’ll visualize reseller counts by country-region and business type using bar charts to analyze B2B channel performance.

In this lecture, you will learn how to calculate Average Quantity Ordered per End-User Customer and Average Quantity Ordered per Reseller using DAX.

We’ll use the DIVIDE function to safely compute ratios by dividing total quantity sold by the number of distinct customers or resellers.

These measures will help you assess engagement levels and channel performance. Finally, we’ll visualize trends over time using area charts to compare customer and reseller activity.

In this lecture, you will learn how to create a calculated table in Power BI using DAX to identify the Top 10 Products by Revenue.

We’ll use the SUMMARIZE function to group products and calculate total revenue per product, then apply TOPN to return only the top 10 products sorted in descending order by revenue.

Finally, we’ll visualize the results in a table to highlight best-selling products for strategic analysis.

In this lecture, you will learn how to calculate Latest Month Revenue dynamically using DAX with variables and CALCULATE.

We’ll extract the most recent order date from the dataset using MAX, store intermediate values like month and year in variables, and then filter the Date table to match the latest month.

Finally, we’ll display the result in a card visual to track real-time revenue within the available data range.

In this lecture, you will learn how to create a Subtotal Revenue measure using DAX that dynamically responds to slicer selections while ignoring row-level filters from table visuals.

We’ll use CALCULATE combined with ALLSELECTED to ensure the measure respects only user-selected filters from slicers or visuals, making it ideal for subtotal calculations and interactive dashboards.

Finally, we’ll demonstrate how this measure behaves in visuals to show context-aware aggregation.

In this lecture, you will learn how to calculate Total Revenue while preserving only the Product Category filter using DAX. We’ll use CALCULATE combined with ALLEXCEPT to remove all other filters except the one applied to Product[Category].

This approach is ideal for high-level comparisons across categories without being influenced by lower-level filters like product or subcategory.

Finally, we’ll visualize the results in a matrix to identify which categories drive the most revenue.

In this lecture, you will learn how to calculate Total Revenue for Mountain Bikes priced above the overall average List Price using DAX.

We’ll first create a measure to calculate the average List Price across all products using CALCULATE and ALL to ignore filters. Then, we’ll use CALCULATE combined with FILTER to apply row-level logic that includes only Mountain Bikes with a List Price greater than this average.

Finally, we’ll visualize the results in a table to identify premium products driving revenue.

In this lecture, you will learn how to perform a Pareto Analysis (80/20 Rule) in Power BI using DAX. We’ll create two measures:

1. Top 20% Product Label – dynamically labels products as “Top 20%” or “Others” based on their rank by total revenue.

2. Top 20% Product Revenue – calculates the total revenue contributed by the top 20% of products.

We’ll use functions like RANKX, COUNTROWS, TOPN, and CALCULATE to implement this logic.

Finally, we’ll visualize the results to validate whether the top 20% of products contribute approximately 80% of total revenue, helping identify high-impact products for strategic decisions.

By the end of this session, you’ll be able to apply Pareto principles to uncover revenue concentration and optimize business performance.

In this lecture, you will learn how to apply the Pareto Principle (80/20 Rule) to analyze customer segments in Power BI using DAX.

We’ll create two sets of measures for Resellers and End-User Customers:

1. Top 20% Label – dynamically labels each reseller or customer as “Top 20%” or “Others” based on their total revenue ranking.

2. Top 20% Revenue – calculates the total revenue contributed by the top 20% of resellers or end-users.

We’ll use advanced DAX functions like RANKX, COUNTROWS, TOPN, and CALCULATE to implement this logic. Finally, we’ll visualize the results to determine whether the top 20% of customers contribute approximately 80% of total revenue, helping identify high-value customers for strategic focus.

Learn how to calculate End User Customer Lifetime Revenue using DAX functions RELATEDTABLE( ) and SUMX( ). This lecture demonstrates step-by-step logic to retrieve related sales data, apply revenue formulas, and aggregate results for lifetime value analysis.

Learn how to calculate Reseller Lifetime Revenue using DAX functions RELATEDTABLE( ) and SUMX( ). This lecture demonstrates step-by-step logic to retrieve related sales data, apply revenue formulas, and aggregate results for lifetime value analysis.

Learn how to use the RELATED( ) function in Power BI DAX to retrieve values from a related table. This lecture demonstrates creating a calculated column to pull the product list price from the Product table into the Sales table by leveraging one-to-many relationships.

Learn how to activate an inactive date relationship in Power BI using USERELATIONSHIP( ) within CALCULATE( ).

This lecture demonstrates creating a measure to analyze total revenue by Ship Date instead of Order Date, enabling alternate date context for logistics and trend analysis.

Learn how to calculate shipping delays in days using the DATEDIFF( ) function in Power BI DAX.

This lecture covers comparing Due Date and Ship Date, interpreting positive, negative, and zero values, and analyzing logistics performance.

Learn how to calculate the percentage contribution of each product category to total sales orders using DAX functions CALCULATE( ), ALL( ), and DIVIDE( ). This lecture demonstrates creating measures for grand totals, safe division, and interpreting results in a table visual for performance analysis.

Learn how to create a combined measure for total customers by adding distinct end-user customers and resellers. This lecture demonstrates combining existing measures to calculate overall customer reach and visualize it using card visuals for strategic insights.

Learn how to calculate last year’s total revenue using DATEADD( ) and CALCULATE( ) in Power BI DAX.

This lecture demonstrates creating a measure to shift the date context by one year for year-over-year comparisons and visualizing revenue trends side by side.

Learn how to calculate Year-over-Year (YoY) Revenue Growth % in Power BI using DAX. This lecture demonstrates creating a measure to compute percentage change compared to the previous year using DIVIDE( ), referencing current and last year revenue measures, and visualizing trends with a line chart.

Learn the fundamentals of Cohort Analysis in Power BI using the Adventure Works dataset. This lecture explains what cohorts are, why they matter in business intelligence, and how they help analyze customer retention, revenue trends, and marketing effectiveness over time.

Learn how to identify the first purchase date for each customer using DAX in Power BI. This lecture demonstrates creating a calculated column with CALCULATE( ), MIN( ), and FILTER( ) to return the earliest order date for cohort analysis.

Learn how to create a Cohort Month for each customer in Power BI using DAX. This lecture demonstrates using the FORMAT( ) function to convert the first purchase date into a year-month format, enabling grouping customers into monthly cohorts for retention and trend analysis.

Learn how to create an Order Month column for each transaction in Power BI using DAX. This lecture demonstrates using the FORMAT( ) function to convert order dates into a year-month format, enabling monthly grouping of transactions for cohort-based analysis.

Learn how to link sales transactions to customer cohorts using the RELATED( ) function in Power BI DAX. This lecture demonstrates adding a calculated column that retrieves the Cohort Month from the Customer table, enabling cohort-based analysis at the transaction level.

Learn how to calculate Cohort Index in Power BI using DAX. This lecture demonstrates using DATEDIFF( ) and DATEVALUE( ) to compute the number of months since a customer’s first purchase, helping track retention and behavior over time.

Learn how to create a Retention Matrix measure in Power BI using DAX. This lecture demonstrates using CALCULATE( ) and DISTINCTCOUNT( ) to count unique customers by Cohort Month and Cohort Index, enabling visualization of customer retention patterns over time in a matrix format.

Learn how to create a revenue measure for the Cohort Matrix in Power BI using DAX.

This lecture demonstrates leveraging an existing Total Revenue measure to calculate revenue by Cohort Month and Cohort Index, enabling analysis of revenue trends across cohorts over time.

Learn how to calculate Retention Rate (%) in Power BI using DAX. This lecture demonstrates using VAR, CALCULATE( ), DISTINCTCOUNT( ), and DIVIDE( ) to compute the percentage of customers retained from the original cohort, enabling accurate retention analysis in a matrix visual.

Learn the fundamentals of RFM Analysis in Power BI using the Adventure Works dataset. This lecture explains how to segment customers based on Recency (R), Frequency (F), and Monetary (M) values, helping identify high-value customers, loyal buyers, and those at risk of churn.

Learn how to calculate Recency in Power BI using DAX for RFM Analysis. This lecture demonstrates using CALCULATE( ), MAX( ), ALL( ), and DATEDIFF( ) to compute the number of days since a customer’s last purchase, helping identify recent activity and engagement levels.

Learn how to calculate Frequency for RFM Analysis in Power BI using DAX. This lecture demonstrates using RELATED( ), SELECTEDVALUE( ), CALCULATE( ), DISTINCTCOUNT( ), and FILTER( ) to count the number of unique purchase transactions per customer, helping identify frequent buyers.

Learn how to calculate Monetary value for RFM Analysis in Power BI using DAX. This lecture demonstrates using SELECTEDVALUE( ), CALCULATE( ), and an existing Total Revenue measure to compute the total sales amount per customer, enabling segmentation based on spending behavior.

Learn how to assign RFM scores (1 to 5) for Recency, Frequency, and Monetary values in Power BI. This lecture explains why scoring is important for standardizing metrics, segmenting customers into groups (e.g., Champions, At Risk), and enabling ranking for marketing and retention strategies.

Learn how to create an RFM Segment Code in Power BI using DAX. This lecture demonstrates combining Recency, Frequency, and Monetary scores into a 3-digit code (e.g., 555) using the FORMAT( ) function and concatenation, enabling easy grouping and targeting of customer segments.

Learn how to create RFM Segment Labels in Power BI using DAX. This lecture demonstrates translating 3-digit RFM codes (e.g., 555, 431) into readable category names like “Champions” or “At Risk,” making customer segments easy to understand and actionable for marketing and retention strategies.