DAX in Power BI
DAX (Data Analysis Expressions) serves as a formula language within Power BI, aiding analysts in efficient data analysis and visualization. Classified into Numeric (e.g., integers, currency) and Other (e.g., strings, binary objects) data types, DAX encapsulates its code within functions. This approach streamlines report design, empowering users to harness dataset capabilities effectively, enhancing both analysis precision and report creation efficiency.
DAX Functions
In Power BI, a diverse range of DAX functions are available to analyze data and generate new columns and measures. These functions can be divided into various categories, as shown below.
Aggregate Functions
Below is a tabular format listing commonly used DAX Power BI functions in the Aggregate category, along with their syntax or formula -
| Function | Syntax | Description |
| SUM | SUM(column) | Returns the sum of numeric values in a column. |
| MIN | MIN(column) | Returns the minimum value from a column. |
| MAX | MAX(column) | Returns the maximum value from a column. |
| AVERAGE | AVERAGE(column) | Calculates the average of numeric values in a column. |
| SUMX | SUMX(table, expression) | Returns the sum of an expression evaluated for each row in a table. |
Counting Functions
Below is a tabular format listing popular counting functions used in DAX Power BI -
| Function | Syntax | Description |
| COUNTROWS | COUNTROWS(table) | Counts the number of rows in a table. |
| DISTINCTCOUNT | DISTINCTCOUNT(column) | Counts the number of distinct values in a column. |
| COUNT | COUNT(column) | Counts the number of rows in the specified column that contain non-blank values. |
| COUNTBLANK | COUNTBLANK(column) | Counts the number of blank values in a column. |
| COUNTAX | COUNTAX(table, expression) | Counts the number of rows in a table that has a non-blank value for the specified expression. |
Logical Functions
Below is a tabular format listing popular logical functions used in DAX Power BI -
| Function | Syntax | Description |
| AND | AND(logical test 1, logical test 2) | It checks if all logical expressions are true and returns true if they are; otherwise, it returns false. |
| OR | OR(logical test 1, logical test 2) | Checks if at least one logical expression is true and returns true if it is, otherwise, returns false. |
| IF | IF(logical test, value if true, value if false) | Evaluates a logical test and returns one value if the condition is true or another value if the condition is false. |
| NOT | NOT(logical test) | Negates a logical expression and returns true if the expression is false, otherwise, returns false. |
| SWITCH | SWITCH(expression, value1, result1, value2, result2, ..., default result) | Evaluates an expression against multiple values and returns the corresponding result. |
Text Functions
Below is the tabular format listing commonly used DAX Power BI functions in the Text category, along with their syntax or formula -
| Function | Syntax | Description |
| REPLACE | REPLACE(old text, start num, num chars, new_text) | Replaces a specified number of characters in a text string with new text. |
| SEARCH | SEARCH(find text, within text, [start num]) | Searches for a substring within a text string and returns its starting position. |
| UPPER | UPPER(text) | Converts all characters in a text string to uppercase. |
| LEN | LEN(text) | Returns the number of characters in a text string. |
| CONCATENATE | CONCATENATE(text1, text2) | Combines two or more text strings into a single text string. |
Date Functions
Below is a tabular format listing popular date functions used in DAX Power BI -
| Function | Syntax | Description |
| TODAY | TODAY() | Returns the current date. |
| NOW | NOW() | Returns the current date and time. |
| DATEADD | DATEADD(start date, number of intervals, interval) | Returns a new date after adding a specified number of intervals (days, months, etc.) to the start date. |
| DATEDIFF | DATEDIFF(start date, end date, interval) | Calculates the difference between two dates based on the specified interval (days, months, etc.). |
| YEAR/MONTH/DAY/WEEKDAY | YEAR/MONTH/DAY/WEEKDAY(date) | Returns the year/month/day/weekday component of a date. |
Information Functions
Below is a tabular format listing popular information functions used in DAX Power BI -
| Function | Syntax | Description |
| ISBLANK | ISBLANK(value) | Checks if a value is blank (null) and returns TRUE if it is. Otherwise, it returns FALSE. |
| ISTEXT | ISTEXT(value) | Checks if a value is text and returns TRUE if it is. Otherwise, it returns FALSE. |
| ISNUMBER | ISNUMBER(value) | Checks if a value is a number and returns TRUE if it is. Otherwise, it returns FALSE. |
| ISTABLE | ISTABLE(value) | Checks if a value is a table and returns TRUE if it is. Otherwise, it returns FALSE. |
| ISDATE | ISTEXT(value) | Checks if a value is a date and returns TRUE if it is. Otherwise, it returns FALSE. |
DAX Calculation Types
In Power BI, DAX enables the creation of two fundamental types of calculations, as mentioned below -
Calculated columns
In DAX, calculated columns are new columns that users can add to a table in Power BI. These columns are defined using DAX formulas and are computed row by row, resulting in a static value for each row in the table. Calculated columns extend the data model by incorporating custom calculations based on existing column values. For instance, a calculated column to calculate the total cost of an order could be defined as TotalCost = Order[Quantity] * Order[UnitPrice]. The calculated column will compute the total cost for each row in the 'Order' table based on the given formula.
Calculated measures
In DAX, calculated measures are custom aggregations or calculations performed on existing data in a Power BI report. Unlike calculated columns, calculated measures provide dynamic results that adjust based on the report's filters and context. For example, a calculated measure to calculate total sales could be defined as Total Sales = SUM(Sales[Amount]). This measure will dynamically compute the sales sum based on the report's applied filters and context.
Conclusion
- DAX (Dynamic Analysis Expressions) in Power BI is a powerful formula language that enhances the platform's data analysis and modeling capabilities.
- Users can leverage DAX functions across various categories, such as Aggregate, Text, Date, Logical, Counting, and Information, to perform complex calculations and create custom metrics.
- Calculated columns enable the addition of user-defined static columns to a table, while calculated measures provide dynamic, context-aware aggregations, allowing for flexible data analysis in reports.