DAX stands for Data Analysis Expressions, and is a formula language used in Microsoft Power BI, Power Pivot for Excel, and Analysis Services. It is used to create formulas that can be used to manipulate data and create complex calculations and models. It can be used to create measures, calculate values, filter data, and more.
Audience
This tutorial is intended for anyone who is interested in learning about Microsoft DAX functions for Power BI and Excel. This might include data analysts, business intelligence professionals, or anyone with an interest in data analysis and data modeling.
Prerequisites
This DAX Functions Tutorial assumes that you have a basic understanding of the core concepts of Power BI and data modeling. If you are new to Power BI, you should start by reading the Power BI documentation and tutorials from Microsoft. Additionally, you should have a basic understanding of DAX, the language used for data analysis in Power BI. If you are not familiar with DAX, you can read our DAX Basics Tutorial to get up to speed.
DAX Functions – Introduction
DAX (Data Analysis Expressions) is a powerful language used to create calculated columns and measures in Power BI, Excel, and Analysis Services. DAX functions are similar to Excel functions but are designed specifically to work with relational data. DAX functions can be used to perform calculations on data in a table, create aggregations, and filter data. DAX functions are also used to create calculated columns and measures, which can be used to manipulate and analyze data. DAX functions can be used in Power BI, Excel, and Analysis Services to create calculated columns and measures, filter data, and perform complex calculations.
What is a DAX Function?
DAX (Data Analysis Expressions) is a language used in Power BI, Power Pivot for Excel, and Analysis Services Tabular to define calculations and queries. DAX functions are used to create calculations and expressions in Power BI, Power Pivot for Excel, and Analysis Services Tabular. They provide an efficient way to perform data analysis and calculation tasks. Examples of DAX functions include SUM, AVERAGE, MAX, MIN, and COUNT.
Excel Functions vs. DAX Functions
Excel functions are designed to operate on data stored in tables or ranges and can be used to manipulate and analyze data. DAX (Data Analysis Expressions) is a library of functions and operators that can be used to create formulas and expressions in Power BI, Power Pivot for Excel, and Analysis Services. DAX is designed to work with data stored in a tabular format and can be used to perform calculations and perform data analysis. DAX functions are typically more powerful than Excel functions and can be used to perform complex calculations on large datasets.
Similarities Between Excel Functions and DAX Function
Both Excel functions and DAX functions are used to perform calculations in a data set and generate results from the calculations. They both use formulas to calculate values, and they both allow users to customize the formulas to meet their needs. Additionally, both Excel functions and DAX functions support the use of variables. However, Excel functions are limited to a single sheet, while DAX functions can be used across multiple sheets and tables.
Differences Between Excel Functions and DAX Functions
The main difference between Excel functions and DAX functions is that Excel functions are used to create formulas and calculations for worksheets, while DAX functions are used to create and manipulate data within a Power Pivot or Power BI data model. Excel functions are designed to work on a single cell or range of cells, while DAX functions can work on entire tables or columns of data. Excel functions use spreadsheet-style syntax, while DAX functions use a more structured syntax. Excel functions are designed to work with data that is stored in a tabular format, while DAX functions are designed to work with data that is stored in a multidimensional format. Excel functions are designed to work on a single worksheet, while DAX functions can be used to generate reports and calculations across multiple tables in a data model.
DAX Parameter Naming Conventions
DAX parameters are typically named using the following convention:
1. The name should be descriptive and provide a clear indication of what the parameter is and what it does.
2. The name should be short and concise.
3. The name should be written in all lowercase and with no spaces.
4. The name should include the type of parameter it is (e.g. Boolean, Integer, String).
5. If the parameter takes a list of values, it should include the word “list” in the name.
6. The parameter should be prefixed with a “p_” or “param_” to indicate that it is a parameter.
7. If the parameter is part of a specific function, its name should include the name of the function.
Types of DAX Functions
1. Statistical Functions: These functions perform statistical calculations such as AVERAGE, COUNT, MIN, MAX, etc.
2. Logical Functions: These functions help to test specific conditions and return appropriate values such as IF, AND, OR, etc.
3. Date and Time Functions: These functions are used to manipulate date and time values such as DATE, TIME, YEAR, etc.
4. Text Functions: These functions are used to manipulate text strings such as CONCATENATE, LEFT, RIGHT, etc.
5. Math Functions: These functions are used to perform mathematical calculations such as SUM, ABS, POWER, etc.
6. Aggregation Functions: These functions are used to calculate aggregations such as SUMX, AVERAGEX, etc.
7. Iterator Functions: These functions are used to iterate through a table and perform calculations such as SUMMARIZE, FILTER, etc.
DAX Table-Valued Functions
DAX Table-Valued Functions are predefined functions in DAX (Data Analysis Expressions) which return a table data type. These functions can be used to perform calculations, create relationships, and perform various other operations. Examples of DAX Table-Valued Functions include SUMMARIZE, FILTER, and TOPN. These functions can be used to create dynamic calculations, apply filters, and work with data in a more efficient manner.
DAX Aggregation Functions
DAX aggregation functions are used to summarize data from a table or related tables. These functions can be used to calculate sums, averages, minimums, maximums, counts, and other statistical information about the data in the table. Examples of DAX aggregation functions include SUM(), AVERAGE(), MIN(), MAX(), COUNT(), COUNTA(), DISTINCTCOUNT(), and STDEV().
DAX Filter Functions
1. FILTER: This function is used to filter a table by applying a condition to the data. It returns only the rows that meet the condition.
2. CALCULATE: This function is used to modify the filter context of a table or expression by applying a condition to the data. It calculates a new expression for each row in the table, based on the condition provided.
3. ALL: This function is used to filter all the rows in a table. It returns a table with all the rows, regardless of the filter condition.
4. ALLEXCEPT: This function is used to filter out all the rows in a table, except those that meet the specified condition. It returns only the rows that meet the condition.
5. KEEPFILTERS: This function is used to maintain the filter context of a table or expression, even if other filters are applied to it. It preserves the current filter context and applies additional filters.
DAX Time Intelligence Functions
DAX Time Intelligence functions are a set of functions used to manipulate data in a Power BI data model. These functions allow for complex calculations to be made on date and time fields in order to provide insights into trends, patterns, and relationships in the data. These functions are used for forecasting, forecasting trending, and analyzing data over different time frames. Examples of DAX Time Intelligence functions include TOTALYTD, SAMEPERIODLASTYEAR, and DATESYTD.
DAX Date and Time Functions
1. DATEADD: This function is used to add a specified time interval to a given date.
Syntax: DATEADD (interval, number, date)
2. DATEDIFF: This function is used to calculate the difference between two given dates.
Syntax: DATEDIFF (datepart, startdate, enddate)
3. DATENAME: This function is used to return the specified part of a given date
Syntax: DATENAME (datepart, date)
4. DATEPART: This function is used to return an integer representing the specified part of a given date.
Syntax: DATEPART (datepart, date)
5. GETDATE: This function is used to return the current system date and time.
Syntax: GETDATE ()
DAX Information Functions
DAX information functions allow users to retrieve information about a data model in the Power BI, Excel, or Analysis Services. Examples of information functions are CALCULATE, CALCULATETABLE, and VALUES. These functions can be used to return information about the table or column in the data model. Additionally, they can be used to calculate measures or evaluate expressions. Furthermore, these functions can be used to identify current filter context, analyze performance, or to create relationships between tables.
DAX Logical Functions
1. IF: This function is used to evaluate logical conditions and return one value if the condition is TRUE and another value if it is FALSE.
2. AND: This function is used to test multiple conditions at the same time and returns TRUE if all conditions are TRUE, and FALSE if any of the conditions are FALSE.
3. OR: This function is used to test multiple conditions at the same time and returns TRUE if any of the conditions are TRUE, and FALSE if all conditions are FALSE.
4. NOT: This function is used to reverse the logical value of its argument. If the argument is TRUE, NOT returns FALSE; if the argument is FALSE, NOT returns TRUE.
DAX Math and Trig Functions
DAX Math and Trig functions are designed to perform a variety of calculations related to mathematical and trigonometric operations. These include functions to calculate the sum, average, minimum, maximum, and other statistical values for a set of data. They also include functions to calculate trigonometric values such as sine, cosine, and tangent. Additionally, DAX Math and Trig functions can be used to perform operations such as rounding, absolute value, and squaring.
DAX Parent and Child Functions
DAX parent and child functions are used to create hierarchies within data sets, such as creating a parent-child relationship between categories and subcategories. These functions can be used to create visualizations in Power BI and Excel, as well as to perform calculations and query data. Examples of DAX parent and child functions include RELATED, PATH and PATHCONTAINS. RELATED is used to retrieve related values from a parent table; PATH is used to create a hierarchal path of related values; and PATHCONTAINS is used to check if a specific value is included in the path.
DAX Statistical Functions
1. AVERAGE: Calculates the average of all the numbers in a specific column.
2. MEDIAN: Calculates the median of all the numbers in a specific column.
3. MODE: Calculates the mode of all the numbers in a specific column.
4. PERCENTILE: Calculates the specific percentile of all the numbers in a specific column.
5. COUNT: Counts the number of values in a specific column.
6. MIN: Calculates the minimum number in a specific column.
7. MAX: Calculates the maximum number in a specific column.
8. SUM: Calculates the sum of all the numbers in a specific column.
9. STANDARD DEVIATION: Calculates the standard deviation of all the numbers in a specific column.
10. VARIANCE: Calculates the variance of all the numbers in a specific column.
DAX Text Functions
DAX text functions include:
–CONCATENATE: Combines two text values together.
–CONCATENATEX: Combines multiple text values together.
–EXACT: Compares two text values and returns TRUE if they are exactly the same.
–FIND: Locates one text value within another text value and returns the position of the start of the text.
–LEFT: Returns the leftmost characters from a text value.
–LEN: Returns the number of characters in a text value.
–LOWER: Converts a text value to all lowercase.
–MID: Returns a specific number of characters from a text value, starting at a specific position.
–REPT: Repeats a text value a specified number of times.
–RIGHT: Returns the rightmost characters from a text value.
–SEARCH: Locates one text value within another text value and returns the position of the start of the text.
–SUBSTITUTE: Replaces existing text with new text in a text string.
–TRIM: Removes extra spaces from a text value.
–UPPER: Converts a text value to all uppercase.
DAX Other Functions
DAX also contains other functions such as:
* Statistical functions
* Logical functions
* Time intelligence functions
* Math and Trigonometry functions
* Text functions
* Information functions
* Filter functions
* Aggregation functions
* Date and Time functions
* Parent and Child functions
* Deprecated functions
DAX Function Description Structure
A DAX function description structure provides a summary of the syntax, usage, and details of a specific DAX function. It includes the name of the function, its purpose, the parameters it takes, and the type of data it returns. It also provides examples of how the function may be used in a calculation. Additionally, it may provide links to additional resources or related functions.
DAX Parameter Naming Conventions
DAX parameters follow standard SQL naming conventions. They should be written in all lowercase, with underscores as separators.
Examples:
• table_name
• column_name
• parameter_name
DAX Parameter Name & Description
1. NR_OF_THREADS – This parameter defines the number of threads used to execute a query in a parallel manner.
2. MAX_DOP – This parameter defines the maximum number of threads that can be used to execute a query.
3. MAX_MEMORY_PER_NODE – This parameter defines the maximum amount of memory that can be allocated to each node for query execution.
4. MAX_MEMORY_PER_QUERY – This parameter defines the maximum amount of memory that can be allocated to a query for its execution.
5. MAX_QUERY_COST – This parameter defines the maximum cost of a query that can be executed in a given time frame.
6. MAX_PARALLELISM – This parameter defines the maximum degree of parallelism that can be used for query execution.
Prefixing Parameter Names or Using the Prefix Only
When prefixing parameter names, it is important to make sure that the prefix is not used for any other purpose than to identify the parameter. Additionally, when using the prefix only, it is important to make sure that the prefix is unique enough to identify the parameter accurately. This can be done by choosing a prefix that is specific to the parameter and is unlikely to be used for any other purpose.
DAX Functions – Description Structure
DAX functions are a collection of functions and operators that can be used in formulas and expressions to query and manipulate data in Analysis Services Tabular models. These functions are used to perform different types of calculations and analysis such as aggregations, filtering, and time intelligence. They are also used to define calculated columns and measures, as well as create relationships between tables and provide additional contextual information. Each DAX function has a specific syntax and requires specific parameters in order to be effective. A description of each function includes a brief overview of the purpose of the function, a list of the parameters required, and example usage.
Description
DAX (Data Analysis Expressions) is a formula language used in Power BI, PowerPivot, and Analysis Services Tabular. It helps users create powerful calculations to gain insights into their data. DAX functions can be used to calculate and analyze data, create dynamic sets and tables, and even perform statistical operations on data. Examples of DAX functions include SUM, AVERAGE, MAX, MIN, COUNTROWS, and CALCULATE. These functions can be used to perform basic calculations, create measures, and create calculated columns. Additionally, DAX functions can be used to filter data and create dynamic sets. They can also be used to create relationships between tables and create custom calculations.
Syntax of DAX Functions
The syntax of DAX functions follows the pattern of:
[FunctionName(argument1, argument2, …, argumentn)]
For example, to calculate the sum of a column called “Total Sales”, the syntax would be:
SUM([Total Sales])
Parameter
DAX (Data Analysis eXpressions) is a formula language used for data analysis in Power BI, Power Pivot, and other data analysis tools. It is a library of functions, operators, and constants that can be used to build formulas to calculate values in a dataset. DAX provides a wide range of functions used for creating measures and calculated columns, filtering data, and working with time-intelligence. It also provides functions for text and statistical calculations, and for analyzing relationships between data.
Return Value
The return value of a DAX expression depends on the type of expression. For example, a DAX expression can return a table, a single value, or a scalar value. In the case of a table, the return value is the set of rows and columns that define the table. A single value expression will return a single value, such as a number, text string, or date. A scalar value expression will return a single value, such as a number, text string, or date.
DAX Functions – Aggregation
DAX provides several aggregation functions, which can be used to calculate a single value from a table or column. These include SUM, AVERAGE, MIN, MAX, COUNT, and COUNTX. These functions can be used to calculate the total, average, or minimum/maximum values of a column, or count the number of rows in a table.
Following are the DAX Aggregation functions
1. SUM: Computes the sum of all the values in a column.
2. AVERAGE: Calculates the average of all the values in a column
3. MAX: Returns the maximum value from a column.
4. MIN: Returns the minimum value from a column.
5. COUNT: Counts the number of rows in a column.
6. COUNTROWS: Counts the number of rows in a table.
7. DISTINCTCOUNT: Counts the number of distinct values in a column.
8. PRODUCT: Computes the product of all the values in a column.
9. STDEV.P: Calculates the population standard deviation of a set of values.
10. VAR.P: Calculates the population variance of a set of values.
DAX Functions – Filter
A DAX function used to filter data is the FILTER() function. This function takes a table as its first argument and an expression as its second argument. The expression is used to determine which rows in the table are returned by the FILTER() function. The syntax for the FILTER() function is:
FILTER(<table>,<expression>)
For example, the following DAX statement uses the FILTER() function to return a subset of a table called “Product” where the product type is “Desktop”:
FILTER(Product,Product[Type]=”Desktop”)
Following are the DAX Filter functions
1. FILTER: Returns a table that contains only the rows that satisfy a given logical expression.
2. ALL: Returns all the rows in a table or all the values in a column, ignoring any filters that might have been applied.
3. ALLEXCEPT: Returns a table that contains all the rows in a table except for those that are filtered by one or more columns.
4. VALUES: Returns a one-column table that contains the distinct values from the specified column.
5. KEEPFILTERS: Returns a table that contains only rows that the filter context may be applied to.
6. TOPN: Returns the top (most frequent) values from a table or column based on the specified criteria.
7. RELATED: Returns a single column of related values from another table.
8. CALCULATETABLE: Returns a table resulting from applying a filter expression to another table.
9. SUMMARIZE: Returns a summary table for the requested totals over a set of groups.
10. DISTINCT: Returns a one-column table that contains the distinct values from the specified table or column.
DAX Functions – Time Intelligence
TIME_SERIES_INTRADAY
This function returns a time series of intraday prices for a given security. It takes three parameters:
1. Security – The identifier of the security to be queried.
2. Interval – The interval of the time series, in minutes.
3. Start Date/Time – The start date and time of the time series.
TIME_SERIES_DAILY
This function returns a time series of daily prices for a given security. It takes three parameters:
1. Security – The identifier of the security to be queried.
2. Start Date/Time – The start date and time of the time series.
3. End Date/Time – The end date and time of the time series.
TIME_SERIES_WEEKLY
This function returns a time series of weekly prices for a given security. It takes three parameters:
1. Security – The identifier of the security to be queried.
2. Start Date/Time – The start date and time of the time series.
3. End Date/Time – The end date and time of the time series.
TIME_SERIES_MONTHLY
This function returns a time series of monthly prices for a given security. It takes three parameters:
1. Security – The identifier of the security to be queried.
2. Start Date/Time – The start date and time of the time series.
3. End Date/Time – The end date and time of the time series.
Following are the DAX Time Intelligence functions:
1. TOTALYTD: Returns the Total YTD value for the given dates.
2. TOTALQTD: Returns the Total QTD value for the given dates.
3. TOTALMTD: Returns the Total MTD value for the given dates.
4. PARALLELPERIOD: Returns the value of the expression evaluated in a previous period, defined by offsetting the current period by a specified number of intervals.
5. PREVIOUSYEAR: Returns the value of the expression evaluated in the same period one year ago.
6. SAMEPERIODLASTYEAR: Returns the value of the expression evaluated in the same period one year ago.
7. DATESINPERIOD: Returns a table containing a column of dates representing a given period, such as the current month or current quarter.
8. DATESBETWEEN: Returns a table containing a column of dates representing a period defined by two dates.
9. DATESYTD: Returns the dates within the current year to date.
10. DATESMTD: Returns the dates within the current month to date.
11. DATESQTD: Returns the dates within the current quarter to date.
12. PREVIOUSQUARTER: Returns the value of the expression evaluated in the same period one quarter ago.
13. PREVIOUSMONTH: Returns the value of the expression evaluated in the same period one month ago.
DAX Functions – Date and Time
Following are the DAX Date and Time functions
1. TODAY()
2. NOW()
3. HOUR()
4. MINUTE()
5. SECOND()
6. DATEVALUE()
7. DATENAME()
8. DATEADD()
9. DATEDIFF()
10. YEAR()
11. MONTH()
12. DAY()
13. WEEKNUM()
14. MONTHEND()
15. MONTHSTART()
16. ENDOFMONTH()
17. DATE()
18. TIME()
19. TIMEVALUE()
20. FORMAT()
DAX Functions – Information
DAX stands for Data Analysis Expressions, a formula language used in Microsoft’s Power BI and Power Pivot. DAX functions are the core of the language used to create calculations and expressions in Power BI. DAX functions are similar to Excel functions but are designed specifically for the handling of relational data and complex modeling.
These functions support the use of Power Pivot, Power Query, and Power BI Desktop for data analysis and reporting. DAX includes a variety of functions that provide helpful calculations such as sum, average, and count, as well as more complex expressions that help to create powerful data models.
DAX is a powerful language for creating formulas and expressions. It can be used to calculate summaries, averages, and other aggregate values, as well as to create complex models that can be used to analyze and report on data. With the help of DAX functions, users can quickly and easily create powerful models and reports that can be used to make better decisions.
Following are the DAX Information functions which is used to retrieve information about the data model, columns, and measures in the Data Model.
1. DATATABLE – Returns a table with information about the columns in the specified table.
2. DATATYPE – Returns a string specifying the data type of the specified column.
3. HASONEFILTER – Returns a Boolean value indicating whether one or more filters are applied to the specified column.
4. HASONEVALUE – Returns a Boolean value indicating whether one or more values are selected in the specified column.
5. HASROLLUPS – Returns a Boolean value indicating whether the specified column has any rollup enabled.
6. ISCROSSFILTERED – Returns a Boolean value indicating whether the specified column is cross-filtered.
7. ISEMPTY – Returns a Boolean value indicating whether the specified table is empty.
8. ISFILTERED – Returns a Boolean value indicating whether the specified column is filtered.
9. ISINSCOPE – Returns a Boolean value indicating whether the specified column is in the current filter context.
10. ISSELECTEDMEASURE – Returns a Boolean value indicating whether the specified measure is selected.
11. MEASUREGROUPLEVEL – Returns an integer indicating the group level of the specified measure.
12. RELATEDTABLE – Returns a table related to the specified table.
DAX Functions – Logical
DAX has a number of logical functions including IF, AND, OR, NOT, TRUE, and FALSE. These functions can be used to evaluate conditions and take action based on the results. For example, IF can be used to check whether a condition is true or false and take action based on the result. AND and OR can be used to create more complex logical evaluations. NOT can be used to reverse the evaluation of a logical expression. TRUE and FALSE are used to return either a true or false value.
Following are the DAX Logical functions
1. IF
2. AND
3. OR
4. NOT
5. SWITCH
6. TRUE
7. FALSE
8. IN
9. EXACT
10. CONTAINS
DAX Mathematical and Trigonometric functions
DAX includes the following mathematical and trigonometric functions:
– ABS: Returns the absolute value of a number.
– ACOS: Returns the arccosine, or inverse cosine, of a number.
– ACOSH: Returns the inverse hyperbolic cosine of a number.
– ASIN: Returns the arcsine, or inverse sine, of a number.
– ASINH: Returns the inverse hyperbolic sine of a number.
– ATAN: Returns the arctangent, or inverse tangent, of a number.
– ATANH: Returns the inverse hyperbolic tangent of a number.
– CEILING: Rounds a number up, to the nearest integer or to the nearest multiple of significance.
– COS: Returns the cosine of an angle, expressed in radians.
– COSH: Returns the hyperbolic cosine of a number.
– DEGREES: Converts radians to degrees.
– EXP: Returns e raised to the power of a given number.
– FLOOR: Rounds a number down, to the nearest integer or to the nearest multiple of significance.
– INT: Rounds a number down to the nearest integer.
– LOG: Returns the logarithm of a number to a specified base.
– LOG10: Returns the base-10 logarithm of a number.
– PI: Returns the numeric value of π.
– POWER: Returns the result of a number raised to a power.
– RADIANS: Converts degrees to radians.
– RAND: Returns a random number between 0 and 1.
– ROUND: Rounds a number to the specified number of digits.
– SIGN: Returns the sign of a number.
– SIN: Returns the sine of an angle, expressed in radians.
– SINH: Returns the hyperbolic sine of a number.
– SQRT: Returns the square root of a number.
– TAN: Returns the tangent of an angle, expressed in radians.
– TANH: Returns the hyperbolic tangent of a number.
DAX Functions – Parent and Child
DAX functions are used to query and manipulate data in a Power BI model. They can be used to create or modify tables, calculate values, or perform other tasks. The two main types of DAX functions are Parent and Child.
Parent functions are the main, or “top-level” functions that provide the primary commands for querying and manipulating data in a Power BI model. They are the entry point for most DAX queries and can be used to create or modify tables, calculate values, or perform other tasks. Examples of Parent functions include SUM, AVERAGE, FILTER, and CALCULATE.
Child functions are the functions that are used in conjunction with a Parent function to perform more complex calculations and manipulations. Child functions are often used to refine or extend the capability of a Parent function. Examples of Child functions include RANKX, TOPN, and VALUES.
Following are the DAX Parent and Child functions
DAX Parent Functions:
1. RELATED: This function retrieves related values from another table.
2. RELATEDTABLE: This function returns a related table for use in a calculation.
3. CALCULATE: This function modifies or adds filter context to a calculation by using one or more specified filter expressions.
4. FILTER: This function returns a table that has been filtered using the specified criteria.
5. SUMMARIZE: This function summarizes the data in the underlying table.
6. TOPN: This function returns the top N values from a column.
7. RANKX: This function returns a ranking based on the value of an expression evaluated for each row in a table.
DAX Child Functions:
1. ALL: This function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
2. ALLEXCEPT: This function returns the entire table or column except for the specified values.
3. VALUES: This function returns a one-column table that contains the distinct values from the specified column.
4. UNION: This function combines two or more tables into a single table.
5. INTERSECT: This function returns a table that contains the rows that are common to two or more tables.
6. DISTINCT: This function returns a one-column table that contains the distinct values from the specified column.
7. DIVIDE: This function divides one number by another number.
DAX Functions – Statistical
Following are the DAX Statistical functions:
1. AVERAGE: Calculates the average of all the numbers in a column.
2. COUNT: Counts the number of rows in a table.
3. MAX: Returns the maximum value in a column.
4. MIN: Returns the minimum value in a column.
5. MEDIAN: Calculates the median of all the numbers in a column.
6. STDEV.P: Calculates the population standard deviation of a given set of numeric values.
7. STDEV.S: Calculates the sample standard deviation of a given set of numeric values.
8. VAR.P: Calculates the population variance of a given set of numeric values.
9. VAR.S: Calculates the sample variance of a given set of numeric values.
10. PERCENTILE.INC: Calculates the kth percentile of a given set of values, where k is the percentile value.
11. TOPN: Returns the top number of rows of the specified table, as defined by the expression.
DAX Functions – Text
1. CONCATENATE: Combines two or more text strings into one text string.
2. LEN: Returns the length of a given text string, including all characters and spaces.
3. FIND: Locates one text string within another text string and returns its starting position.
4. LEFT: Extracts a given number of characters from the left side of a text string.
5. RIGHT: Extracts a given number of characters from the right side of a text string.
6. MID: Extracts a given number of characters from a text string, starting at a specified position.
7. REPLACE: Replaces existing characters of a text string with new characters.
8. TRIM: Removes spaces from text strings.
9. UPPER: Converts all characters of a text string to uppercase.
10. LOWER: Converts all characters of a text string to lowercase.
Following are the DAX Text functions used to manipulate the text in Power BI
1.CONCATENATEX: Concatenates the values of a table or columns using the specified delimiter.
2.COMBINEVALUES: Combines the values of multiple columns or tables into a single string.
3.GENERATESERIES: Generates a table of series of dates.
4.FORMAT: Formats a number, date, or time value according to the specified format.
5.UPPER: Converts a text string to uppercase.
6.LOWER: Converts a text string to lowercase.
7.TRIM: Removes leading and trailing spaces from a text string.
8.REPT: Repeats a text string for a specified number of times.
9.REPLACE: Replaces part of a text string with another text string.
10.UNICHAR: Returns the Unicode character for the specified number.
11.SUBSTITUTE: Substitutes new text for old text in a text string.
12.CONCATENATE: Joins together two text strings.
13.LEFT: Returns the leftmost characters from a text string.
14.RIGHT: Returns the rightmost characters from a text string.
15.FIND: Finds one text value within another text value and returns the number of the starting position of the found text.
DAX Functions – Other
DAX functions can also be used for a variety of other tasks, such as creating and managing relationships, creating calculated columns, and creating measures.
Following are the DAX Other functions
1. DISTINCT
2. DIVIDE
3. EXCEPT
4. INTERSECT
5. SELECTEDVALUE
6. TOPN
7. VALUES
8. XNP
9. UNION
10. ALL