Do you know about the Power BI DAX Function? Microsoft introduced its visualization tool in 2013 and named it as Power BI. Power BI is a cloudbased platform and can be handled beautifully to give the best visualization to even the most critical business data.
It helps in evaluating and sharing the data and gets connected to all the data sources of the users. Users can have their own scalable dashboard so that they can choose the best visualization blueprint for themselves. Visualization can be enabled by using the navigation to enable simple drag and drop of the data. Learn about Power BI Functionality and Features.
FREE ENROLLMENT
POWER BI FOR DUMMIES
What is DAX
DAX is the abbreviated form of Data Analytics Expressions. It means that it is a type of formulae or expressions that are used for the analysis and calculations of data. The combination or collection of different expressions such as constants, operators, and functions form a formula to give results or output. To check the list of examples check Power BI DAX functions with examples pdf.
Download Power BI DAX Cheat sheet
Need of DAX in Power BI
In Power BI, we can read data from the database. The table can also be merged in Power BI. It is seen that the data from columns are readily available for plotting but it is not possible that the data will always be available in the same way.
Suppose we want to improve our dashboard and for that, we need to change or modify our data. Examples help in undesrtanding the topic clearly check Power BI DAX functions with examples pdf. Consider a situation that in Power BI the modifications that we require is not readily available. It is simple we will have to perform some modifications on the available data. Thus, to help perform this kind of modifications on the data Power BI DAX function comes into the picture.
Power BI DAX function helps in finding more detailed information from raw data. Long things cut short is Data Manipulation is done with the help of DAX. Analysts can come up with fresh insights by discovering new ways for data calculation with the help of DAX. A smart and intelligent approach is provided to the data analysis in Power BI by using DAX.
Key points about DAX
 A DAX function that can be executed may contain value references, nested functions, conditional statements, etc. The complete code of DAX will always be a function.
 Numeric and NonNumeric or others are the two primary data types in DAX formulae. Currency, decimals, integers, etc. come under numeric data type. Whereas, binary objects and strings come under nonnumeric data type.
 The procedure of evaluating a DAX function is that the evaluation starts from inside to outside. This makes the DAX formulating important.
Getting started with Power BI DAX
To understand the usage and importance of DAX in detail and how exactly it is helpful in Power BI, let us take an example to understand it fully.
Let us assume that we want to make a visual in Power BI that will analyze the percentage of growth across all states in a country or want to compare growth/sales yearly. For this kind of calculations, the data is not sufficient that are imported in data tables. We have to perform some operations on the data to make it in a usable form. This work is done by using Power BI DAX function. Still confused about creating the first Power BI Dashboard? Refer the steps once again.
Syntax of Power BI DAX Formula
Any language can be learned in the best way by understanding its elements after splitting them. Thus, before studying any language we study its syntax first. The following is the DAX formula. Let us understand its syntax through its elements.
 TotalSales defines the name of the new measure.
 = (equals to sign) is an operator equating two sides and starting of DAX formula.
 SUM is the DAX function.
 Parenthesis() is used to define arguments and enclose it in the service. Every formula should have at the least one argument compulsorily. Sheet1 is the name of the table. [Gross Sales Amt] is the name of the field. The function will apply itself in the areas of the table name mentioned. To understand the syntax more clearly read the Power BI DAX functions with examples pdf.
Thus, this Power BI DAX formula finds the sum of the product of Gross sales Amount of three years of total sales data.
Note: D is used to represent that this formula is DAX related formula for our understanding.
Calculation Types of DAX
Formulas in DAX can also be called as calculations because ultimately, they are performing calculations to give a resultant value that can be used. Calculated Columns and Calculated Measures are the two calculations that are mainly performed via DAX in Power BI.
Calculated Columns
A new column is created in the existing table by Calculated Columns. At least one function is there in the calculated column rather than the regular one. Whenever you want a column with sorted or filtered information, these calculated columns are used.
Steps to create a Calculated Column:

 Click on Modelling tab in Power BI and select New Option.
 A formula bar will open. In that, you can replace the Column word in it by the name of your column.
 Enter the equation to the righthand side of the equals to sign.
Calculate Measures
Calculated measures will create a field having aggregated values such as averages, percentages, ratios, sum, etc.
Steps to create a Calculated Measures:

 Click on Modelling tab in Power BI and select New Measure tab.
 A formula bar will open. In that, you can replace the Measure word in it by the name you want.
 The expression which needs to be calculated can be entered on the right side of the equals to sign.
 The measure name can be modified by the calculator icon (?) next to it.
Power BI DAX Functions
Calculations performed on the values provide in the arguments are by DAX functions. The arguments in the function should be in a particular order. The cases can be a logical value such as true or false, another formula or function, constants, texts, numbers or reference of the column. The functions perform a particular operation on the enclosed values in arguments. to find more examples refer Power BI DAX functions with examples pdf. DAX formula allows the use of more than one case.
Important Features of DAX functions
To understand DAX functions better, one should know these features.
 DAX function will never refer to individual values; it will refer to a whole column or field or table. To apply DAX function on personal costs, filters need to be involved in the formula.
 Time intelligent features are used to calculate date/time ranges. Such category of functions reincluded in DAX functions.
 One can create its method which can be applied in row by row basis in DAX functions. The context of the values decides the application of equations or applications in each row. Understand DAX functions more clearly with Power BI DAX functions with examples pdf.
 Sometimes DAX formulas need all the values from the full table which is returned by DAX functions, but you are not able to display the contents of this table.
Types of Power BI DAX functions
The following will be the DAX Power BI formula list.
Time Intelligent Functions
If we need to find out values over a fixed period like years, quarters, months, weeks, days, etc. are called time intelligent DAX functions. You can determine the period by these functions and compare the scenarios in the report. List of Power BI DAX functions as follows:
 NEXTMONTH
 NEXTDAY
 LASTNONBLANK
 NEXTQUARTER
 LASTDATE
 FIRSTNONBLANK
 FIRSTDATE
 ENDOFYEAR
 ENDOFQUARTER
 ENDOFMONTH
 DATESYTD
 DATESQTD
 DATESMTD
 DATESINPERIOD
 DATESBETWEEN
 DATEADD
 CLOSINGBALANCEYEAR
 CLOSINGBALANCEQUARTER
 CLOSINGBALANCEMONTH
Date and Time Functions
The calculations to be carried out on date and time values are called date and time DAX functions. These functions always possess Date Time datatype. List of DAX functions as follows:
 YEARFRAC
 YEAR
 WEEKNUM
 WEEKDAY
 TODAY
 TIMEVALUE
 TIME
 SECOND
 NOW
 MONTH
 MINUTE
 HOUR
 EOMONTH
 DAY
 DATEVALUE
 DATEDIFF
 DATE
 CALENDERAUTO
 CALENDER
Logical Functions
The expression that can be evaluated logically and return only one value of either true or false and are base on the conditions are met or not are called logical DAX functions. List of DAX functions as follows:
 TRUE
 SWITCH
 OR
 NOT
 IN
 IFERROR
 IF
 FALSE
 AND
Information Functions
These functions provide information about the values in rows and columns and are called Information DAX functions. It returns the value of TRUE or FALSE after evaluating a given condition. If the evaluated situation comes out to be an error, then ISERROR returns TRUE. List of DAX functions as follows:
 USERNAME
 LOOKUPVALUE
 ISTEXT
 ISONORAFTER
 ISODD
 ISNUMBER
 ISNONTEXT
 ISLOGICAL
 ISINSCOPE
 ISEVEN
 ISERROR
 ISBLANK
 IN Operator/ CONTAINSROW function
 CUSTOMDATA
 CONTAINS
Statistical Functions
The functions which carry aggregation and statistical operations on data values are called as Statistical DAX Functions. List of DAX functions as follows:
 MINX
 MINA
 MIN
 MEDIANX
 MEDIAN
 MAXX
 MAXA
 MAX
 GEOMEANX
 GEOMEAN
 GENERATEALL
 GENERATE
 DIST
 DISTINCTCOUNT
 CROSSJOIN
 COUNTX
 COUNTROWS
 COUNTBLANK
 COUNTAX
 COUNTA
 COUNT
 CONFIDENCE.T
 CONFIDENCE.NORM
 CHISQ.INV.RT
 CHISQ.INV
 BETA.INV
 BETA.DIST
 AVERAGEX
 AVERAGEA
 AVARAGE
 APPROXIMATEDISTINCTCOUNT
 ADDCOLUMNS
Mathematical and Trigonometric Functions
The functions which carry out mathematical calculations on the values are known as Mathematical and Trigonometric DAX functions. List of DAX functions as follows:
 FLOOR
 FACT
 EXP
 EVEN
 DIVIDE
 DEGREES
 CURRENCY
 COSH
 COS
 COMBINA
 COMBIN
 CEILING
 ATANH
 ATAN
 ASINH
 ASIN
 ACOSH
 ACOS
 ABS
ParentChild Functions
The data values that are the part of parent and child hierarchy are called as parentchild DAX functions. List of DAX functions as follows:
 PATHLENGTH
 PATHITEMREVERSE
 PATHITEM
 PATHCONTAINS
 PATH
Text Functions
String functions in excel matches to the text DAX functions in Power BI. List of DAX functions as follows:
 VALUE
 UPPER
 UNICHAR
 TRIM
 SUBSTITUTE
 SEARCH
 RIGHT
 REPT
 REPLACE
 MID
 LOWER
 LEN
 LEFT
 FORMAT
 FIXED
 FIND
 EXACT
 CONCATENATEX
 CONCATENATE
 COMBINEVALUES
 CODE
 BLANK
Table Functions
The functions that apply conditions and operations on the entire table are known as Table Functions in DAX formula. The input in other arguments or expressions comes from the output of table functions used in a DAX formula. The relationship between that table is retained due to the results of these functions. List of DAX functions as follows:
 RELATEDTABLE
 DISTINCT
 VALUES
 FILTER
 ALL
Other Functions
How to Create a Power BI Dashboard from a Report. List of DAX functions as Follows:
 VAR
 UNION
 TREATAS
 Table Constructor
 SUMMARIZECOLUMNS
 SELCTEDMEASURENAME
 SELECTEDMASUREMASTERSTRING
 SELECTEDMEASURE
 NATURALLEFTOUTERJOIN
 NATURALINNERJOIN
 ISSELECTEDMEASURE
 ISEMPTY
 INTERSECT
 GROUPBY
 GENERATESERIES
 EXCEPT
 ERROR
 DATATABLE
DAX Context
Complete the understanding of the DAX language, and it is necessary to know the concept of DAX Context. It will also help in understanding how DAX works in Power BI. Filter context and Row context are the two types of DAX Context in Power BI.
Filter Context:
If we want to focus on specific values within a row or wish to apply filters on particular benefits from a row, rather than choosing the entire row, filter context comes in use. Thus, row context, along with filter context, is used when we want to focus on applying the calculations to the specific values of a table. When functions such as ALL, CALCULATE, RELATED, FILTER, ETC. is applied to the values filter context is to be used.
Row Context:
In the row context, we apply a filter to a specific row in a table to carry out DAX calculations. This row context applies the formula for the count on a current row. It is mainly used to measure rows.
Conclusion
We have covered all the essential topics required in Power BI to work with DAX formulae. To learn and use DAX funtions easily it is very important to go through Power BI DAX functions with examples pdf. You might have gained the basic knowledge of Power BI DAX. You can now build a variety of DAX formulae and apply it to your existing tables to create more advanced reports. Also, go through the complete Power BI tips and tricks to create promising reports.