Key Concepts in DAX:
Formulas: DAX expressions are written as formulas that perform calculations on tables and columns in the data model. DAX formulas are similar to Excel formulas but optimized for working with tables and related data.
Evaluation Context: DAX calculations are context-aware, meaning the results depend on the current context defined by row and filter context in the data model.
Row Context: When DAX is calculated for each row in a table, it works in a row context, focusing on a single row at a time.
Filter Context: DAX expressions also work in a filter context, where filters from slicers, filters, and other visualizations affect the results of the calculation.
Calculated Columns: These are columns added to a table, derived from DAX expressions, and calculated for each row in the table. Calculated columns are computed during data refresh and stored in the data model.
Measures: Measures are dynamic aggregations calculated on the fly based on the filter context. They aggregate data across the entire data model or within a particular scope.
Context Transition: When a formula references a column from another table, it transitions to a new evaluation context based on the relationship between the tables.
Filter and Row Context Interaction: Understanding the interaction between filter and row context is crucial for writing accurate and efficient DAX calculations.
Common DAX Functions:
DAX provides a wide range of functions to perform calculations and transformations on data. Some common DAX functions include:
Aggregation Functions: SUM, AVERAGE, MIN, MAX, COUNT, etc.
Time Intelligence Functions: TOTALYTD, TOTALQTD, TOTALMTD, DATEADD, DATESYTD, etc.
Information Functions: ISBLANK, ISNUMBER, ISTEXT, etc.
Filter Functions: FILTER, ALL, ALLEXCEPT, etc.
Logical Functions: IF, AND, OR, NOT, etc.
Date and Time Functions: TODAY, NOW, YEAR, MONTH, etc.
Text Functions: CONCATENATE, LEFT, RIGHT, UPPER, etc.
Statistical Functions: VAR, STDEV, etc.
Best Practices for DAX:
Use Measures over Calculated Columns: Use measures for aggregations to reduce data model size and improve performance.
Use Iterator Functions Sparingly: Iterator functions (e.g., SUMX, AVERAGEX) can be resource-intensive, so use them judiciously.
Avoid Circular Dependencies: Avoid circular references between calculated columns and measures.
Optimize Performance: Use SUMMARIZE and other functions to create optimized DAX expressions.
Use Good Naming Conventions: Use clear and descriptive names for measures and columns.
Test and Debug: Test your DAX formulas and use tools like DAX Studio for debugging.
Examples:
1.Total Sales Amount = SUM('Sales'[Amount])
2.Average Sales Price = AVERAGE('Sales'[Price])
3.YTD Sales = TOTALYTD([Total Sales Amount], 'Date'[Date])
4.Running Total Sales =
CALCULATE(
[Total Sales Amount],
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
5.Sales by Product Category =
SUMMARIZE(
'Sales',
'Product'[Category],
"Total Sales", [Total Sales Amount]
)
6.Customer Churn Rate =
DIVIDE(
COUNTROWS(FILTER('Customers', 'Customers'[LastPurchaseDate] < TODAY())),
COUNTROWS('Customers')
)
7.Sales Rank =
RANKX(ALL('Product'[ProductID]), [Total Sales Amount],,DESC,Dense)
8.Sales Growth =
DIVIDE(
[Total Sales Amount] - CALCULATE([Total Sales Amount], SAMEPERIODLASTYEAR('Date'[Date])),
CALCULATE([Total Sales Amount], SAMEPERIODLASTYEAR('Date'[Date]))
)
