Practicing DAX (Data Analysis Expressions) functions is essential for mastering Power BI and Excel data modeling. DAX is a formula language that allows you to create custom calculations and aggregations in your data models. Below are some common DAX functions along with examples and sample data to help you get started:
Sample Data: Let's consider a simple sales table with the following columns:
Date: The date of the sales transaction.
Product: The name of the product sold.
Quantity: The quantity of the product sold in each transaction.
Price: The price of each product sold.
Examples of DAX Functions:
SUM: Calculates the sum of a numeric expression over a table.
Total Sales = SUM(Sales[Quantity] * Sales[Price])
This DAX formula calculates the total sales amount by multiplying the Quantity and Price columns for each row and then summing the results.
AVERAGE: Calculates the average of a numeric expression over a table.
Average Price = AVERAGE(Sales[Price])
This DAX formula calculates the average price of all products sold.
COUNTROWS: Counts the number of rows in a table or table expression.
Total Transactions = COUNTROWS(Sales)
This DAX formula calculates the total number of transactions in the sales table.
FILTER: Filters a table based on a specified condition.
Filtered Sales = FILTER(Sales, Sales[Price] > 10)
This DAX formula creates a new table that includes only the rows where the price is greater than 10.
RELATED: Returns a related value from another table based on a specified relationship.
Product Category = RELATED(Products[Category])This DAX formula returns the product category for each row in the sales table, based on the relationship between the Sales and Products tables.
CALCULATE: Evaluates an expression in a modified filter context.
Discounted Sales = CALCULATE(SUM(Sales[Quantity] * Sales[Price]), Sales[Price] > 10)This DAX formula calculates the total sales amount for transactions where the price is greater than 10.
These examples demonstrate some basic DAX functions and how they can be applied to the sample data. As you practice and gain familiarity with DAX, you can explore more advanced functions and combinations to perform complex calculations and analysis in your data models. Happy DAX-ing! 📈💡
