Most useful DAX functions In Power Pivot

#1- Calculate()

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>…)

Example-

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.

#2- Filter()

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.

Syntax- FILTER(<table>,<filter>)

Example-

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 top DAX functions 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>)

Example-

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.

#4- Related()

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.

Syntax- RELATED(<column>)

Example-

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)

Example- MIN([SalesMargin])

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.

#6- Max()

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>)

Example– MAX(OnlineSales[ProfitAmount])

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).

#7- Average()

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)

Example- AVERAGE(OnlineSales[ProfitAmount])

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 top DAX functions in Power BI.