DAX In Power BI

What is DAX?

DAX stands for Data Analysis Expression. It is used for data analysis and calculations. DAX is a collection of functions, operators and constants. All these are evaluated as one formula to get the result. These formulas are very useful in Business Intelligence tools like Power BI. In DAX, complete code is written inside a function. So, it is called a functional language.

A DAX expression that is executable may contain nested functions, conditional statements, value references, etc. DAX formulas have two primary data types: numeric and others. The numeric data types include currency, decimals, etc., whereas others include string and binary objects. In a DAX formula, we can use values of the mixed data types as inputs. The conversion will take place during the time of formula execution automatically. As per your instructions, the output values will be converted into data types. The data scientists can use the data sets in DAX to the fullest. They can discover new ways to calculate data values with the help of DAX. In DAX, expressions are evaluated from the innermost function going to the outer function one by one. 

Become a Power BI Certified professional by learning this HKR Power BI Training !

What is Power BI?

Power BI is a Business Intelligence and Data Visualization tool used to convert data from data sources into interactive dashboards and analysis reports. For interactive visualizations, BI offers a cloud-based service. It provides a simple interface for the end-user. With the help of this, the end user can create their reports and dashboards. For different platforms, different versions of Power BI like Desktop, mobile Power BI, and Service-based apps are used. For Business Intelligence, it provides multiple services and software connectors.

Use of DAX in PowerBI: 

In Power BI, we can use DAX for calculated columns, Measures, and Tables. Computed columns allow us to create new columns based on the data given. 

For example, if you want to add the “Final Price” column in the table, then the DAX function is used to calculate the new column only if the quantity and price is available.

EX:

Price = List_Items[Quantity]*List _Items[MRP]

Here each row will have its calculated value.

We can also perform calculations using measures without adding any data. This is helpful for reports. Here the price can be displayed without the need for adding a new column to store it. 

EX:

Total MRP column*Total Quantity Column.

DAX functions used on tables return the entire tables. For example, to generate a list of the countries in which the organization has its clients use the function -

Cities touched = DISTINCT (Customers[City])

Basic knowledge of Power BI Desktop for a user is enough to create reports with all the available data. But if you want to use advanced calculations in the Power BI reports, you need DAX. 95% of Power BI potentials as an analytical tool is missed if you don't use DAX. For example, if you want to make a visual to analyze growth percentages across different states of a country, the data fields that you import are not enough for that purpose. For this, new measures using DAX Language are to be made. DAX with Power makes the data analysis an innovative and intelligent approach.

Click here to learn Power BI Tutorial

Power BI Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

Some of the Useful functions of DAX in PowerBI:

DAX functions are predefined formulas that perform the calculation on values provided to it in arguments. Every function performs a particular operation on the enclosed values in an argument. In a DAX formula, you can use one or more arguments. Some of the other functions of DAX are:

Date and Time functions:

Calculations on date and time values are performed by date and time functions. The data type of these functions is the datetime data type always. Some of the Date and Time functions are CALENDARAUTO, CALENDAR, DATE, DATEVALUE, DATEDIFF, DAY, HOUR, MINUTE, EOMONTH, MONTH, SECOND, NOW, TIMEVALUE, TIME, TODAY, WEEKDAY, WEEKNUM, YEARFRAC, YEAR.

Time Intelligence Functions:

These functions are used to evaluate values over a fixed period, such as years, quarter, months, weeks, days, etc. You can compare two scenarios in your report by specifying the time using these functions. Some of the Time Intelligence Functions are CLOSINGBALANCEYEAR, CLOSINGBALANCEMONTH, CLOSINGBALANCEYEAR, DATESINPERIOD, DATESBETWEEN, DATEADD, DATESQTD, DATESYTD, DATESMTD, ENDOFYEAR, ENDOFMONTH, ENDOFQUARTER, FIRSTNONBLANK, FIRSTDATE, LASTDATE, LASTDATE, NEXTQUARTER, NEXTMONTH, NEXTDAY.

Information Functions: 

Information Functions provide information related to the data values in the rows and columns. For the given values, it evaluates the given condition in the functions and returns True or False. Some of the Information Functions are CUSTOMDATA, CONTAINS, CONTAINSROW, ISERROR, ISBLANK, ISINSCOPE, ISEVEN, ISODD, ISNUMBER, ISLOGICAL, ISNONTEXT, ISTEXT, ISONORAFTER, USERNAME, LOOKUPVALUE.

Logical Functions: 

These functions are used to logically evaluate an expression or argument and return true or false based on the condition. Some of the Logical Functions are TRUE, FALSE, AND, OR, IF, IFERROR, IN, NOT, SWITCH.

Mathematical and Trigonometric Functions: 

These are the functions that are used to perform all sorts of mathematical functions on the values referred. Some of the math and trigonometric DAX functions available in PowerBI are ACOS, ACOSH, ABS, ASINH, ASIN, ATANH, ATAN, COMBINA, COMBIN, COS, COSH, DEGREES, CURRENCY, EVEN, EXP, DIVIDE, FACT, FLOOR.

Subscribe to our YouTube channel to get new updates..!

Subscribe

Statistical Functions: 

The functions used to carry out aggregation and statistical functions on data values in a DAX expression in Power BI. Some of the Statistical DAX functions available in PowerBI are APPROXIMATEDISTINCTCOUNT, ADDCOLUMNS, AVERAGEX, AVERAGEA, AVERAGE, COUNT, COUNTA, COUNTX, MAX, MAXA, MAXX, MEDIAN, MEDIANX, MIN, MINA, MINX, GEOMEAN, GEOMEANX, GENERATE, GENERATEALL, CROSSJOIN, DISTINCTCOUNT, etc. 

Text Functions:

These functions are similar to the string functions of Excel. These functions are used to evaluate string values. Some of the text DAX functions available in Power BI are: CODE, BLANK, COMBINEVALUES, CONCATENATEX, CONCATENATE, EXACT, FIND, FORMAT, FIXED, LEN, LEFT, LOWER, MID, REPLACE, RIGHT, REPT, SUBSTITUTE, SEARCH, TRIM, UNICHAR, VALUE, UPPER. 

Parent-child functions:

These are the functions used for the data values that are part of a parent-child hierarchy. Some of the Parent-child functions DAX functions available in Power BI are PATH, PATHLENGTH, PATHITEM, PATHCONTAINS, PATHITEMREVERSE.

Table functions: 

These functions are used to apply operations and conditions on entire tables. The output generated from table functions is used as the input in other arguments or expressions in a DAX formula. The results of these functions retain relationships between the table columns. Some of the table functions in Power BI are ALL, VALUES, FILTER, DISTINCT, RELATEDTABLE.

There are some functions that are very useful but do not fall under any category: ERROR, EXCEPT, GENERATESERIES, DATATABLE, GROUPBY, INTERSECT, ISEMPTY,ISSELECTEDMEASURE,NATURALINNERJOIN,NATURALLEFTOUTERJOIN,SELECTEDMEASURE,TREATAS,UNION,VAR,SELECTEDMEASUREFORMATSTRING, SELECTEDMEASURENAME, SUMMARIZECOLUMNS.

Click here to get latest Power BI Interview Questions and Answers for 2022

Some of the important DAX functions are: 

Average: This function is used to find the average from a given set of values. 
Ex:

Avgcomm - AVERAGE(List_Items[Price])

Max: This function is used to find the maximum from a set of values.
Ex:

Highsale = MAX(List_Items[Price])

Min: This function is used to find the minimum from a set of values.
Ex:

Lowestsale = MIN(List_Items[Price])

Count: This function is used to count any numerical values.
Ex:

TicketVolume - COUNT(Invoices[Ticket])

Concatenate: This function is used to join values in calculated columns. You can use ConcatenateX if you are using measures.
Ex:

ProMrp = CONCATENATE(List_Items[Items], List_ Items[MRP]

TotalYTD: This function is used to calculate the sum from the beginning of the current year to a specific date. Calculations are performed based on the calendar date, not according to the financial year.
Ex:

Cumisales = TOTALYTD(SUM(List_Items[Price]) , Invoices[Date])

All: This function returns everything. It ignores filters.

Conclusion 

In this blog, we have learnt about DAX, Power BI, and the benefits of DAX in Power BI. DAX functions can perform all the advanced calculations in Power BI. Using DAX functions in Power BI allows us to use most of the Power BI potentials. I hope you found this blog helpful. If you have any queries related to DAX in Power BI, you can comment below.

Related Articles:

• KPI in Power BI
• Looker vs Power BI
• MSBI vs Power BI