Summary
Highlights
The `DATESYTD` function is introduced to calculate year-to-date accumulated sales. The example demonstrates creating a new measure to show monthly sales growth year-to-date in a matrix and a line chart, allowing for comparison of accumulated sales across different years and months.
The `LOOKUPVALUE` function is explained as a way to retrieve a value from another table based on a primary and foreign key relationship, similar to a VLOOKUP. An example demonstrates adding 'product names' to the sales table using `LOOKUPVALUE`, even though the sales table originally only contained product codes.
The session begins by reviewing different types of DAX formulas: aggregation, date/time, filter, logical, text, information, and mathematical functions. It recalls modeling options like 'new measure', 'quick measure', and 'new column', emphasizing that 'new measure' and 'quick measure' are for numerical calculations, while 'new column' is better for text, conditional, or row-by-row expressions.
The structure of a DAX formula is explained: measure name, equals sign, function, and then table and column names. The `AVERAGE` function is introduced, demonstrating how to calculate the average of sales for different categories using a 'new measure' in Power BI. This involves adding quantity and sales to a table and then creating a new measure to calculate the average of sales.
The concepts of `COUNT` (total row count) and `DISTINCTCOUNT` (unique value count) are clarified with an Excel example. In Power BI, `COUNT` and `DISTINCTCOUNT` are applied to the 'product code' column in the sales table to illustrate the difference between counting all occurrences and counting only unique occurrences of product codes within each category.
The `MAX` and `MIN` functions are covered, showing how to find the maximum and minimum values in a column. An example demonstrates their use with sales data, filtering by 'accessories' category to identify the highest and lowest priced products.
The `SUMX` function is explained as a way to perform row-by-row calculations and then sum the results. The concept of 'syntax' and 'parameters' in DAX is introduced, likening syntax to a recipe and parameters to ingredients. An example creates a 'SUMX' measure to calculate total sales by multiplying price by quantity for each row and summing them.
The `DATE` function is introduced to artificially create a date column when only year, month, and day components are available. This is done using a 'new column' in Power BI, taking year, month number, and day columns from a 'dates' table.
The `DATEDIFF` function is used to calculate the difference between two dates in various units (years, months, days, etc.). A practical example involves creating a 'birthdays' table manually in Power BI to calculate the age or difference in days, months, or years between a birth date and today's date.
The `WEEKNUM` and `WEEKDAY` functions are demonstrated. `WEEKNUM` identifies the week number within a year (1-52), while `WEEKDAY` returns the day of the week (e.g., 1 for Monday, 7 for Sunday), with different system options for starting days. Both are applied to birth dates to show their practical use.
The `CALCULATE` function, a powerful filter function, is extensively discussed. It allows applying filters to an expression. An example shows how to calculate 'London sales' by summing sales and filtering the 'city' column in the 'geography' table for 'London'. This highlights the efficiency of `CALCULATE` compared to manual filtering in Excel. The concept of circular dependency when creating artificial columns is explained, leading to the use of 'new measure' for calculating percentages of filtered data.
The difference between 'context filters' (slicers) and 'explicit filters' (DAX formulas) is established. The `ALL` function ignores all context filters, providing a total unaffected by external selections. `ALLSELECTED`, conversely, respects context filters while still providing a total within the selected context. Practical examples illustrate how `ALL` provides a grand total across all years regardless of the year filter, while `ALLSELECTED` adjusts its total based on the selected year, allowing for percentage calculations relative to the current filter context.
Conditional functions `IF`, `BLANK`, `AND`, and `OR` are detailed. `IF` allows defining a logical test and resulting values for true or false outcomes. `BLANK` is used to represent empty values. `AND` (represented by `&&`) requires all conditions to be true, while `OR` (represented by `||`) requires at least one condition to be true. Examples are given for creating 'income level' categories using nested `IF` statements and applying `AND` and `OR` to filter data based on country and city, illustrating both the function and operator syntax.
The `CONCATENATE` function is demonstrated, showing how to join two text strings or columns. It's also shown how to achieve the same result using the ampersand (`&`) operator for joining multiple elements. The `FORMAT` function is introduced as a way to display values in a specific format (e.g., currency, percentage), useful for visualization purposes when data types might not be natively supported.
The `CONTAINSSUBSTRING` function is explained as a way to check if a specific text (substring) exists within another text string, returning `TRUE` or `FALSE`. An example shows how to identify products with the word 'short' in their name. This function is then combined with an `IF` statement to categorize products containing 'short' as 'promotional shorts', demonstrating its utility in textual data analysis, especially for call center logs or public sentiment.
The `DIVIDE` function is briefly mentioned as previously covered. The `ROUND`, `ROUNDDOWN` (round towards zero), and `ROUNDUP` (round away from zero) functions are introduced to show how to control numerical precision. Examples are provided to illustrate how these rounding functions behave with different decimal places, particularly relevant for financial or accounting data.
A quick recap of DAX operators is provided, including mathematical operators (+, -, *, /), comparison operators (=, <, >, <=, >=, < > for 'not equal to'), text concatenation (`&`), and logical operators (`&&` for AND, `||` for OR). It's highlighted that conditions can span multiple tables in a relational model. The session concludes with a Q&A segment, addressing questions about nesting `IF` and `OR`, case sensitivity in DAX (indifferent), using empty strings instead of `BLANK`, and Power BI data refresh mechanisms.