7 Useful DAX functions in Power BI you need to know
Do you want to dive into the analysis of your company and product list over different ranges quickly? Are you looking for the best way that helps you customize specific variables?
If yes, then DAX functions in Power BI can help you reach your goals. Before we discuss DAX functions, let’s have a brief intro about Power BI.
Power BI is one of the amazing free tools that helps in visualizing the data and share insight into the company efficiently and quickly. It’s easy to use interface, and excellent features allow the users to track and review business metrics without any challenge.
See this article for more details on Power BI: Beginners Guide to Power BI.
What is DAX and its importance in Power BI
Data Analysis Expressions (DAX) is a syntax language that includes expressions and formulas used for analyzing and calculating data. These expressions are a combination of constants, functions, and operators that leads to accurate results. It would not be wrong if we call it an advanced version of MX excel, having high-end capability of managing and manipulating data.
DAX functions play an essential role in Power BI. It not only allows data analytics to use datasets to its fullest but also will enable them to find new ways that help them in calculating the values and come up with valuable results. All the results that are generated by using DAX functions are both intuitive and discerning.
Now let’s take a rundown at the commonly used DAX functions in Power BI that help you generate better reports.
7 most useful DAX functions in Power BI
Let’s get started….
It is one of the most important functions that help you evaluate the expressions by adding your own filters to data.
Syntax- CALCULATE(<expression>, <filter1>, <filter2>…)
Sum of sales all countries = CALCULATE(SUM(‘Sales'[Sales]),ALL(‘SalesGeography’))
The column that you want to aggregate is represented by the first parameter, whereas the 2nd filter represents a boolean that removes all the filters. This specific function is mostly used with aggregation functions. Moreover, this powerful function acts as a supercharged “IF” statement and provides real insight into your data.
The working of the Filter function is the same as the Calculate function. The major difference between the two is that Filter functions are not mutable and are commonly used to return a subset of expression or table.
Count of sales orders over 200 = COUNTROWS(FILTER(‘Sales’, ‘Sales'[Sales] > 200))
The identification of the expression resulting in a table is represented by the first parameter, whereas the 2nd parameter represents a true/false or boolean expression assessed from each row of the table. If you are thinking of using it as a standalone function, then you might not be able to get the result because this function is used with other functions.
#3- ALL function()
All function is another useful DAX function of Power BI that you should know about. It is basically used to get all the rows and columns in a table irrespective of the applied filters. If you want to calculate the aggregations, then All functions will help you get an accurate value.
Syntax: ALL(<table> or <column>)
Count of grand total sales= COUNTROWS(ALL(‘Sales’))
In this example, ALL functions are applied to the sales table by clearing all the filters that you might have applied earlier. This function plays a great role, especially if you want to calculate denominators for calculating ratios and percentages. Similar to the Filter function, ALL function is used in conjunction and allows you to get a count of grand total sales.
Related is categorized under table function that is applied to the conditions and operations on the entire table. With this function, you can easily return the related value from another table by using the below-mentioned syntax.
Count of sales in the America = COUNTROWS(FILTER(ALL(‘Sales’), RELATED(‘SalesGeography'[Countries]) = “America”))
In this example, the count of sales for America is retrieved from one table to another by establishing a relationship. This particular expression is executed in a row context and helps you retain the relationships between columns of that table. Whenever this function is performed, all the values in the specified table are examined regardless of any filters.
#5- Min ()
By using the Min() function, you can get the smallest value in a column within a blink of an eye. Also, you can get the smallest value between two scalar expressions accurately. Texts, numbers, blanks, and dates are some types of values that are counted in Min function. If you use a blank, then it will be considered as zero(0).
Syntax- MIN (Column Name) or MIN (expression1, Expression2)
The example added above will get you the smallest value from the added column of SalesMargin. Say, if you want to get the result of two scalar expressions, then use two values in this function like- Min([TotalSales], [ResellerMargin]). In this example, you will get the minimum values from the column TotalSales and ResellerMargin.
If you want to extract the maximum value from the column or between two scalar expressions, then the Max() function can help you in the same. Basically, it is totally opposite to the Min() function. It returns the maximum value of your added expression.
Syntax– MAX(<column>) or MAX(<expression1>, <expression2>)
The example added above returns the maximum value found in the ProfitAmount column of the OnlineSales. You don’t have to spend hours to get the maximum value. By applying a single function, you will receive an accurate value.
Say if you want to get the maximum value of two expressions, then use the function like- Max([TotalSales], [ResellerMargin]). This example will get you the maximum values of TotalSales and ResellerMargin. Always remember that if you use a blank value in the expression, then it will be treated at Zero(0).
Are you spending hours on finding the average (arithmetic mean) of all the numbers in a column? If yes, then this function is for you. It allows you to get an average value of all the numbers. While adding non-numeric values like text, remember that no aggregation will be performed, and it returns blank. Also, while adding logical values or empty values, the function will not be performed because all such values are ignored, but if your cell includes zero value, then it will be counted.
Syntax- AVERAGE (Column Name)
The example added above will get you the average values in the column, ProfitAmount, in the table, OnlineSales. In this function, if there are no rows, then it will return a blank value. Talking about MS- Excel, then also it returns zero(0) value if the rows do not meet the conditions. So, always keep such things in mind while using DAX functions in Power BI.
Useful reading- 5 POWER BI ALTERNATIVES
In a Crux
The list of DAX functions in Power BI is quite big. But all the functions that are added above are commonly used and help you make advanced reports and analyze the data without spending extra time and effort.
Hopefully, the points mentioned above help you get a clear understanding of DAX formulas. Hopefully it also makes clear how Power BI differs from Excel. Such functions allow you to get full control of your organizational data and perform the task with full accuracy and professionalism.
Still, if you have any doubts or want to share something about DAX functions, feel free to drop a message in the comments section added below.