LoginGet started

👉Beginner Power BI DAX Functions Tutorial [Full Course]

Pragmatic Works・171 minutes read

The workshop presented by Mitchell Pearson from Pragmatic Works covers Power BI's DAX language, focusing on calculated columns, measures, tables, and navigation functions. Key concepts include managing relationships in data models, utilizing row context, handling semi-additive measures, and manipulating filter contexts for accurate data analysis and visualization in Power BI.

Insights

  • Workshop by Mitchell Pearson from Pragmatic Works, contact via email, YouTube, or blog.
  • Calculated columns in DAX for data description and relationships.
  • Row context in DAX for accurate data selection and deactivating filters.
  • Key columns essential for relationships between tables in Power BI.
  • Utilize related functions in DAX for data navigation and efficient relationships.
  • Semi-additive measures handled using DAX functions like "last date" and "last non-blank."

Get key ideas from YouTube videos. It’s free

Recent questions

  • What is the purpose of calculated columns in DAX?

    Calculated columns in DAX are used to add new columns to tables for data description and relationship building. They provide immediate results in Power BI tables, enhancing data models by offering specific analytical insights. These columns play a significant role in data analysis by creating new data points based on existing information. Calculated columns utilize row context to operate on one row at a time, ensuring accurate data selection and impacting relationships in Power BI data models. They are essential for establishing relationships between tables, with unique key columns like a combination of region and month number being crucial for effective data modeling.

  • How can you create a calculated measure in DAX?

    To create a calculated measure in DAX, you need to define expressions like summing sales amounts or counting rows. This process involves referencing table names for clarity and organization. Calculated measures are essential for modifying filter contexts and evaluating expressions within specific parameters. The calculate function in Power BI allows for manual modifications of filter contexts, starting with an expression, often an aggregation, followed by a filter to narrow down the context. It is recommended to organize calculated measures in folders within tables or a separate measure table to maintain a structured and organized workspace in Power BI.

  • What are semi-additive measures in data modeling?

    Semi-additive measures in data modeling are those that are additive across certain dimensions but not across time. For example, a bank account balance remains constant over days in a month but can be added across different customers. Handling semi-additive measures in Power BI involves techniques like time intelligence calculations, year-to-date, and rolling totals in DAX language. It is crucial to have a date table in the data model that spans all possible dates without gaps for accurate analysis. Various DAX functions like TotalYTD, SamePeriodLastYear, and ParallelPeriod are powerful tools for time intelligence calculations, offering flexibility and ease of use.

  • How can you handle blank returns in DAX calculations?

    To handle blank returns in DAX calculations, you can use functions like "all" to eliminate blanks, especially when a single country member shows up in a table. Another method is to utilize the "remove filters" function to clear filters from specified tables or columns, simplifying the process. Building a percent of total calculation involves dividing total sales by total sales for all countries, providing a simple way to analyze ratios. Understanding how to use "calculate" to ignore filters from specific columns, like country, is crucial for creating accurate percent of total calculations. By modifying filter contexts with "calculate," you can create specific measures like total sales for individual countries, allowing for detailed analysis and comparison.

  • What is the significance of context transition in DAX?

    Context transition in DAX occurs when a row filter is added to the filter context, affecting calculations differently. It is crucial to understand context transition for advanced DAX calculations, as it can simplify or complicate calculations based on the scenario. The calculate function in DAX allows for the addition of row filters to the filter context, enabling easier filtering of data and streamlining complex expressions. Wrapping expressions in calculate can force specific filters on data, such as dates, to improve functionality and code readability. Context transition plays a vital role in DAX calculations and can be effectively managed with the calculate function.

Related videos

Summary

00:00

"Power BI Workshop with Mitchell Pearson"

  • The workshop is presented by Mitchell Pearson from Pragmatic Works, offering contact through email, YouTube, or blog.
  • Mitchell Pearson is the training manager at Pragmatic Works, creating classes and conducting live training events.
  • The workshop is three hours long with a 15-minute break at 12:25 Eastern time and will be recorded on the Pragmatic Works YouTube channel.
  • Questions will be answered after the break, with a follow-up blog and YouTube video focusing on specific queries.
  • Class files provided include slides, code examples, data model, and a completed example, all in the September 2020 version of Power BI Desktop.
  • Calculated columns in DAX add new columns to tables for data description and relationship building.
  • Calculated measures in DAX are used for dynamic aggregations, ratios, percentages, time intelligence, and complex relationships.
  • Calculated tables in DAX are utilized for aggregate tables, date tables, and code validation.
  • Navigation functions in DAX like related table and related allow grabbing data from other tables for calculated columns.
  • A relationship must exist in the data model for navigation functions to work efficiently, with related and related table preferred over lookup value for performance.

12:24

"Master DAX for Powerful Data Analysis"

  • To combine first and last names in DAX, use the ampersand operator and double quotes for a space.
  • Calculated columns in DAX show immediate results in Power BI tables.
  • Row context in DAX allows operations on one row at a time, ensuring accurate data selection.
  • Row context deactivates filters on tables, impacting relationships in Power BI data models.
  • Functions like FILTER and X in DAX work on one row at a time, creating row context.
  • Create age breakdown categories in DAX using nested IF statements for customer analysis.
  • Utilize the FORMAT function in DAX to create a two-digit month and four-digit year column.
  • Calculated columns enhance data models by providing specific analytical insights.
  • Key columns in data tables are crucial for establishing relationships between tables in Power BI.
  • Unique key columns, like a combination of region and month number, are essential for effective relationships in data modeling.

25:08

Creating Relationships for Accurate Data Modeling in DAX

  • To identify the region and month number, columns need to be added to the table.
  • The sales territory key helps determine the region of a transaction.
  • In Excel, a VLOOKUP is used, while in SQL Server, a join is employed to fetch the region.
  • In DAX, a new column is added to the table to extract the region from the sales territory table.
  • The related function is utilized to access the region from the sales territory table due to the row context in DAX.
  • The related function leverages existing relationships in the data model for ease of use.
  • The new column in the table successfully displays the regions obtained from the sales territory table.
  • To dynamically extract the month number, related functions are used to access the date table.
  • The combination of region and month number is achieved using the related function in DAX.
  • The process of creating relationships and utilizing navigation functions in DAX is crucial for accurate data modeling.

37:49

"Dimensional Modeling: Handling Unknown Data in Power BI"

  • In dimensional modeling, when data cannot be assigned to a specific product or sales territory, it is recorded as "not applicable" or "unknown" in the fact table.
  • This allows for accurate reporting even with incomplete data, ensuring sales are recorded and reported.
  • The use of "in a" signifies unknown or not applicable data, while blank entries indicate the data is not applicable.
  • A nested if statement is utilized to manage and maintain this data assignment method in dimensional modeling.
  • Validation is crucial to ensure the method is functioning correctly, especially when building visualizations or reports in Power BI.
  • Calculated columns play a significant role in data analysis, allowing for the creation of new data points based on existing information.
  • Row context and x functions are introduced in DAX to facilitate complex calculations and data manipulation.
  • The process of determining the last purchase date for customers involves navigating through related tables and utilizing x functions.
  • Calculate measures in DAX are essential for modifying filter contexts and evaluating expressions within specific parameters.
  • Filter context in Power BI automatically adjusts data based on active relationships, but calculate allows for manual modifications for precise analysis.

50:34

Efficient DAX Calculations for Power BI

  • Calculate function in Power BI involves defining a modified filter context, starting with an expression, often an aggregation, followed by a filter to narrow down the context.
  • Semi-additive measures in data models are those that are additive across certain dimensions but not across time, a common scenario where adding values across time is inaccurate.
  • An example of a semi-additive measure is a bank account balance, where the balance remains constant over days in a month but can be added across different customers.
  • To handle semi-additive measures in Power BI, techniques like time intelligence calculations, year-to-date, and rolling totals are utilized efficiently in DAX language.
  • Essential for time intelligence calculations in Power BI is having a date table in the data model that spans all possible dates without gaps, ensuring accurate analysis.
  • Various DAX functions like TotalYTD, SamePeriodLastYear, and ParallelPeriod are powerful tools for time intelligence calculations, offering flexibility and ease of use.
  • Creating calculated measures in Power BI involves defining expressions like summing sales amounts or counting rows, with best practices including referencing table names for clarity.
  • Organizing calculated measures in folders within tables or assigning them to a separate measure table aids in maintaining a structured and organized workspace in Power BI.
  • Reusing calculated measures like total sales and total cost in creating new measures, such as profit, streamlines the process and enhances efficiency in DAX calculations.
  • Formatting calculated measures with appropriate data types like currency ensures clear visualization and interpretation of the results in Power BI reports.

01:03:27

"Power BI Measures and Filter Context"

  • Measures must be unique, preventing the creation of the same measure on different tables.
  • Referencing a measure is indicated by purple text, distinguishing it from a column in a table.
  • Formatting measures involves selecting the desired currency, like English currency for total sales and total cost.
  • Creating a calculated measure for profit margin involves dividing profit by total sales, recommending the use of the divide function to handle divide by zero cases.
  • Utilizing the divide function in DAX allows for optional parameters, like specifying an alternate result for divide by zero cases.
  • Validating measures in Power BI involves visualizing them in tables and applying filters to observe dynamic changes.
  • Filter context in Power BI allows for automatic filtering based on relationships in the data model, simplifying data analysis.
  • Slicers in Power BI provide an interactive way to filter data based on specific attributes, altering the displayed values accordingly.
  • Calculated measures work within the current filter context, adapting to applied filters through active relationships in the data model.
  • Active relationships in the data model are crucial for proper filtering and functionality, as demonstrated by the impact of deleting an active relationship on filtering capabilities.

01:32:00

Maximize Power BI functionality for total transactions.

  • Utilize filter context without additional measures
  • Duplicate current page in Power BI Desktop to show total transactions per customer
  • Remove existing table data and add total transactions (count star or count rows)
  • Total transactions count all rows in the internet cells table within filter context
  • Break down total transactions by customer using the customer table
  • Differentiate between calculated columns and measures in Power BI
  • Create a date table using Power Query Editor or DAX with the calendar function
  • Consider using IT department's date table or creating a quick date table in DAX
  • Fiscal calendars may require time intelligence functions in DAX
  • Use the calculate function to modify filter context for total sales across all countries

01:44:34

Maximizing DAX Functions for Total Sales Analysis

  • To handle blank returns for total sales in all countries, use DAX to return total sales for all countries if a value exists.
  • In DAX, the "all" function is commonly used to eliminate blanks, such as when a single country member shows up in a table.
  • To clean up visuals, especially with large datasets, consider using the "remove filters" function in DAX to clear filters from specified tables or columns.
  • Another method to achieve the same result as "all" is to utilize the "remove filters" function, which may be easier to comprehend for some users.
  • Building a percent of total calculation involves dividing total sales by total sales for all countries, providing a simple way to analyze ratios.
  • A built-in function, "percent of grand total," offers a quick way to display percentages without writing complex expressions, but it differs from the percent of total calculation.
  • Understanding how to use "calculate" to ignore filters from specific columns, like country, is crucial for creating accurate percent of total calculations.
  • An alternative to writing complex DAX expressions is using the "show value as percent of grand total" feature, which simplifies the process but may not offer the same flexibility as custom measures.
  • By modifying filter contexts with "calculate," you can create specific measures like total sales for individual countries, allowing for detailed analysis and comparison.
  • Utilizing conditional logic within "calculate" enables the creation of measures like total sales for specific countries, adjusting dynamically based on applied filters for enhanced data analysis.

01:57:35

"Calculated Measures in DAX for Sales"

  • Creating a new calculated measure in DAX is the focus, specifically for total sales in the U.S. and Canada.
  • The measure is named "total sales" and is defined to calculate total sales for the U.S. or Canada using the double pipe delimiter for an "or" condition.
  • An alternative method using the OR function is discussed, highlighting its limitation of only accepting two parameters.
  • The preferred method involves using the IN clause similar to SQL, making the code more readable and manageable by listing the countries within curly brackets.
  • The text emphasizes the importance of managing filters when working with calculated measures, showcasing how to ignore specific filters from the slicer or other tables.
  • Demonstrating the ability to add multiple filters within a calculate expression, the text explains how to ignore filters from the date table while focusing on a specific year like 2008.
  • Moving on to time series analysis, the concept of year-to-date sales is introduced, achieved through the total YTD function in DAX.
  • The process involves providing an aggregation measure like total sales and the date column from the date table to calculate year-to-date sales accurately.
  • The simplicity and effectiveness of the total YTD function are highlighted, showcasing its versatility across different dimensions in the data model.
  • The summary concludes by mentioning the upcoming exploration of fiscal year-to-date calculations and the broader scope of time series analysis in DAX.

02:10:40

"Year-to-date Sales Analysis and Calculations"

  • Total sales at the year level equals year-to-date sales due to the same calculation.
  • Year-to-date cells filter data from January 1st to December 31st for cumulative totals.
  • Adding quarter and month to the table allows for a visual representation of the data.
  • Drill down to the quarter level shows cumulative totals for each quarter.
  • Cumulative totals reset at the start of each new year.
  • Week-over-week analysis in DAX requires a custom calendar setup.
  • Fiscal year-to-date sales can be calculated by specifying the year-end date.
  • Optional parameters in DAX, like filters, can refine calculations further.
  • Calculating weekday sales involves filtering based on day numbers.
  • Prior year sales can be derived using the same period last year pattern in DAX.

02:23:24

Comparing sales data for informed decision-making.

  • Creating a measure called "prior year year-to-date sales" to compare year-to-date sales for the current year with the previous year.
  • The measure allows for visual representation in tables or charts, showing intersecting sales data.
  • Using the formula "calculate year-to-date sales same period last year" to analyze sales performance.
  • Differentiating between "same period last year" and "previous year" measures due to their distinct functionalities.
  • Explaining the complexities of using "previous year" versus "same period last year" in data analysis.
  • Demonstrating the impact of using the correct measure on data accuracy and analysis.
  • Comparing year-to-date sales for the current year with the prior year to assess performance.
  • Analyzing sales data to identify trends and make informed decisions for improvement.
  • Introducing the concept of semi-additive measures like inventory balances and account balances.
  • Utilizing DAX functions like "last date" and "last non-blank" to handle semi-additive measures with data gaps.

02:36:26

Optimizing DAX Calculations with Context Transition

  • To return semi-additive measures, rewrite expressions to get the first day instead of the last.
  • Functions like opening balance month and closing balance month can provide similar functionality.
  • Calculate the opening balance for the month by using the closing balance from the prior month.
  • The DAX function "opening balance month" automatically retrieves the product inventory for the last day of the prior month.
  • The function may return blanks if there are no values for certain dates.
  • To handle blanks, create a calculated measure using "parallel period" to get dates from the prior month.
  • Use "last non-blank" to retrieve the last non-blank date from the prior month for accurate results.
  • Context transition occurs when a row filter is added to the filter context, affecting calculations differently.
  • Calculated columns and measures can yield different results due to context transition.
  • Understanding context transition is crucial for advanced DAX calculations and can simplify or complicate calculations based on the scenario.

02:49:54

Enhancing DAX Functionality with Calculate

  • The calculate function in DAX allows for the addition of row filters to the filter context, simplifying complex expressions and enabling easier filtering of data.
  • Wrapping expressions in calculate can force specific filters on data, such as dates, to streamline code and improve functionality.
  • Context transition in DAX can either simplify code or complicate it, depending on the situation, but can be managed effectively with the calculate function.
  • Attendees are encouraged to download updated class materials, consider on-demand learning courses, and explore upcoming DAX boot camps for further education in the field.
Channel avatarChannel avatarChannel avatarChannel avatarChannel avatar

Try it yourself — It’s free.