Microsoft Power BI desktop is a great agile tool used for data mashup, analysis, and visualization. It uses two primary languages: Power Query formula language, also known as M formula language, and DAX (Data Analysis Expression). The M language is used while importing the data, and DAX is used after importing the data in the data model. In this article, we will focus on some of the useful and regularly used DAX formulas.
DAX forms a critical part of Power BI. While you can do without it in the initial phase and create decent dashboards without using it, as your learning curve grows, along with the organization’s demand, you will need to use DAX. For an in-depth analysis of your data, knowing DAX is inevitable.
Types of DAX Calculation
You can create two types of calculations using DAX – Calculated Columns and Calculated Measures.
Like regular columns, calculated columns create another column in the dataset. However, these are our computations’ results, where we use two or more columns from one table or different tables. These are created for row-wise calculations.
Though a lot like calculated columns, calculated measures do not occupy any physical memory and are considered better when dealing with extensive data. They also do not create any columns. These are generally used when you want to perform dynamic computations on a group of rows. DAX Measures are evaluated against the data source and not against the PIVOT table, i.e., DAX calculates all the measures independently against the source. Another feature of CALCULATED MEASURES is that they are dynamic, so if you change any of the rows or columns, the measure gets automatically updated.
6 Most Important DAX Functions
Let’s now discuss the most commonly used DAX functions. Ask any business analyst consultant, and he will tell you how his life revolves around these!
Calculate is one of the most useful DAX functions in Power BI that enables you to evaluate the expressions by adding filters to data.
Suppose you want to see the Profit Earned from a particular Product Category. You can do it by
Profit from Groceries = CALCULATE(SUM(‘SalesData'[Profit]), ‘SalesData’[ProductCategory]= “Groceries”)
You can also multiple filters, as shown below.
Profit from Confectioneries in the Indian subcontinent.
Profit Confectionaries and India = CALCULATE(SUM(‘SalesData'[Profit]), ‘SalesData’[ProductCategory]= “Groceries”, ‘SalesData’[ProductCategory]= “India”)
The CALCULATE function acts as an IF statement and provides critical insight into your data. The following rules apply to the CALCULATE function:
- The filter parameters cannot refer to measures.
- Any functions that scan or return a table cannot be used.
- You cannot use a nested CALCULATE function with the expression.
- It is usually used with aggregation functions, and despite the filter parameters being optional, at least one needs to be used.
The DAX Calculate function provides high-performance calculations for a vast number of functions.
Reach out to us for interactive dashboards.
You can use the FILTER function either with CALCULATE or any other DAX. It allows you to get the subset of any table. Though it’s a lot like CALCULATE, FILTER isn’t mutable – it can only subset the data.
For instance, you want to know how many students scored above 95; you can do it with the following expression:
Number of Students Scoring More Than 90 = COUNTROWS(FILTER(‘StudentsData’, ‘StudentsData'[Marks] > 90))
We never use the FILTER function as a standalone function, but we use it combined with other functions. In the given example, we have used it to return a subset and then count the results.
Every Excel user would know the importance of the SUM function. We use it in Power BI also to add the values in a column.
To combine two calculations into one, we use the SUMX function.
For instance, if you want to find out the total sales revenue, you can do it in two steps. First, calculate the income by multiplying [UnitPrice] with [OrderQuantity] and then adding it to the SUM function. Or you can do it in one step using the SUMX function mentioned above.
Total Sales Revenue = SUMX(SalesData,SalesData[UnitPrice]*[OrderQuantity])
We use ALL function to get all the values in rows in a table or a column by ignoring all the filters applied. We mostly use it for calculating aggregations for all values in any column or all rows in a table. Like the filter function, we use this function nested with other functions.
ALL(<table> or <column1>, <column2>…)
If we want to remove the filter from the previous example of FILTER Function and see the total number of students appearing for the exam, we create ALL functions.
Total Students Appearing For Exam= COUNTROWS(ALL(‘StudentsData'[Marks])
The ALL function plays a vital role when you need to calculate denominators.
The MIN and MAX function gives the smallest and the largest value in the column. You can also use the MIN or MAX function to get the respective smaller or higher value between 2 scalar expressions.
MIN (<column>, <expression>)
MAX (<column>, <expression>)
When these functions come across blank, they treat it as 0, but the output is blank for positive values. In case it comes across any unreadable value, it returns an error.
MIN and MAX function can recognize Numbers, Texts, and Dates. However, MINA/MAXA cannot identify Text values. For a better understanding of the differences between MIN, MINA, and MINX, visit the link. The same logic applies to MX/MAXA/MAXX.
Let’s look at an example now. Suppose you want to find out the highest marks scored in an exam.
TOTALYTD/ TOTALQTD/ TOTALMTD
Time intelligence functions in DAX are handy for calculating data using the time frame. These functions are convenient as they eliminate the need to use complex codes in calculating aggregations.
Expression identifies the column from which you would like to derive your result. The second option that is the dates refers to the dates column: filter parameter is optional.
Total Quarterly Sales = TOTALQTD(SUM(‘SalesData'[Sales]), ‘SalesData'[Date])