DAX in Power BI
The formulas, functions, operators, and constants that makeup data analysis expressions are what let a user build custom tables, dimensions, and measurements. In addition to returning one or more values, they are used to resolve data analysis issues by establishing new connections between various data variables.
Data analysts may carry out complex calculations and find a hidden pattern in unstructured datasets thanks to the DAX Language, which is quite helpful. A function or nested function with conditional expressions, value references, formulae, loops, and other elements always makes up an expression's whole source code. As they are judged from an expression's interior to outermost function, it is crucial to formulate them correctly.
There are two primary data types in Power BI DAX Functions:
- Numeric: These data types include decimals, integers, currency values, etc.
- Non-numeric: It consists of strings and binary objects, etc.
Importance of DAX in Power BI
It’s important to learn the DAX functions in Power BI as they help you to implement the functionalities like data transformation and visualization with basic knowledge of the Power BI interface, you can create decent reports and share them online. However, for calculations and dimensional analysis, you need to know how Power BI DAX functions are carried out.
For Example, you can calculate the growth percentage and visualize the growth percentage in different regions of a Country to compare the data over the years. DAX in Power BI helps a designer create new measures, which in turn helps a business to identify the problems and find appropriate solutions.
DAX Formula – Syntax
You can grasp any language with ease by breaking a statement down into its component parts. You should learn these expressions' syntax so that you can build new ones that meet your needs.
Test Column = [Units Sold] * [Manufacturing Price] [Segment]
It represents a Formula used to create new columns by multiplying the values of the other columns. Let’s understand clearly what each element does:
- Test Column: It indicates the name of the new measure.
- (=) Sign: It indicates the starting of your DAX Formula.
- [Units Sold] and [Manufacturing Price]: These two are the arguments or columns whose values are used to generate the output.
- (*): The * operator multiplies the values of the two-column variables.
- [Segment]: It represents the classification of the corresponding formula. Unlike regular columns, the calculated columns are necessary to have at least one.
DAX Functions Types
there are several types of functions, including:
- Scalar functions operate on a single value and return a single value. Examples of scalar functions include SUM, AVERAGE, and MAX.
- Table functions operate on a table and return a table or a table expression. Examples of table functions include FILTER, SUMMARIZE, and ADDCOLUMNS.
- Iterator functions perform an operation on each row of a table and return a result. Examples include SUMX and AVERAGEX.
- Logical functions test conditions and return a Boolean value (TRUE or FALSE). Examples include IF and AND.
- Information functions return information about the environment or context in which they are used. Examples include USERNAME and SELECTEDVALUE.
- Filter functions filter a table based on certain conditions. Examples include ALL and VALUES.
DAX Calculation Types
Two different types of calculations or formulas can be utilized with DAX in Power BI to produce a resulting value from input values.
- Calculated Columns: New columns can be combined with existing ones using calculated columns and filters. Power BI Desktop's Modeling Page allows for the creation of additional columns by inputting the names and formulas for those columns.
- Calculated Measures: The user can build fields using aggregate values such as average, ratio, and percentage, among others, using Measures. The measurements are generated from Power BI Desktop's modeling page, just the way computed columns are.
DAX Functions in Power BI are the predefined formulas used to calculate the arguments in a function, executed in a particular order. These arguments could be numbers, constants, texts, other functions or formulas, and logical values such as True or False. The Functions perform a particular operation on one or more arguments in a DAX formula. Below are some key points of DAX Functions
When using DAX functions in Power BI, a full field, column, or table is always referenced rather than a single value. To use DAX functions on specific values, you must first establish filters within the DAX formula.
To determine the time and date ranges, DAX uses the Time Intelligence function. These functions will be covered in more detail below.
Without using any filters, single rows can also have DAX functions applied to them. Depending on the context of each row, the computations can be used.
The whole table may occasionally be returned by these procedures, which can be utilized as input by other Power BI DAX operations.
Here are a few functions that are commonly used in DAX
- Total Quantity = SUM(Sales[Quantity]) - This measure calculates the total quantity of items sold by summing the values in the Quantity column of the Sales table.
- Average Price = AVERAGE(Sales[Price]) - This measure calculates the average price of items sold by averaging the values in the Price column of the Sales table.
- Lowest Price = MIN(Sales[Price]) - This measure returns the lowest price of any item sold by finding the minimum value in the Price column of the Sales table.
- Highest Price = MAX(Sales[Price]) - This measure returns the highest price of any item sold by finding the maximum value in the Price column of the Sales table.
- Number of Products = COUNT(Sales[Product]) - This measure counts the number of different products sold by counting the number of unique values in the Product column of the Sales table.
- Quantity Classification = IF(Sales[Quantity] > 10, "High", "Low") - This measure returns "High" if the quantity of an item sold is greater than 10, and "Low" if it is not.
- Today's Date = TODAY() - This measure returns the current date.
- Current Time = NOW() - This measure returns the current date and time.
- Current Month = MONTH(TODAY()) - This measure returns the current month by using the MONTH function on the result of the TODAY function, which returns the current date.
- Current Year = YEAR(TODAY()) - This measure returns the current year by using the YEAR function on the result of the TODAY function, which returns the current date.