DAX Power BI Tutorial
Table of Contents
There are many expressions and formulas that are used for data analysis and calculations, such expressions are called as DAX. Basically, DAX stands for Data Analysis Expressions. The main aim of such expressions is to get final result by using various combinations of operators, constants and functions. DAX programming language allows to create calculated columns, measures and custom tables. Indeed these DAX Power BI formulas allow data analysts to use various datasets to their full potential. In other words, we can say that DAX helps you to create new information from data already in your model.
Up till now it might be very clear, how useful DAX in Power BI is. An Analyst will need decent knowledge of Power BI to generate simple reports on available data. But if they want to build advanced dashboards with great insights, they have to level up their skills and learn DAX.
Suppose you want to count or visualize performance growth of different department of an organization. In that case, the data fields that you will generally get will not be enough to find out perfect insights. In such situations you need to make new measures using DAX features. Under Modeling tab, you will get feature to add new measure. You can write DAX formula to calculate the value of new measure in formula bar. So, DAX can basically help you to build better visualizations which would eventually provide better insights. Analyst can generate fitting solutions to their business problems which otherwise they might have missed out by using general approach.
Major Features of DAX in Power BI
Power BI has many important features, but DAX could be considered as on of the best features of Power BI. Some of the major features of DAX are mentioned below:
- In DAX the complete code is always a function so it is also called as functional language. Also, DAX expression may contain nested functions, conditional statements, value references etc.
- Framing DAX formula is important because the evaluation starts from innermost functions and then moves outside.
- Data can be of two types in DAX: Numeric and Non-Numeric. The first data type may be integer, float or decimal whereas lateral type can be string or binary objects.
- In DAX, the data type conversion is automatic. Which means, that you can provide elements of different data-type as input and they will get converted automatically during execution of formula. In addition, the output value datatype will be as per your instructions.
DAX Formula Syntax
While learning any programming language it is important to have a command over its syntax. As an illustration we have broken syntax into various elements:
- A is the name of new measure.
- B is the equals sign. This operator indicates start of DAX formula and equates both L.H.S and R.H.S.
- C is a DAX function used to add the value of given field (SalesAmount) from table Sales. In this case the DAX function used is SUM.
- D is Parenthesis and are generally used to define argument, and it’s a must that every function must have atleast one argument.
- E It is the name of table from which a column is used in a formula.
- F It is the name of the field from which the formula will use values.
In this DAX Syntax, system is commanded to calculate the sum of Sales Amount and store it in a new variable called Total Sales.
DAX Calculation Types
DAX Expressions or formulas are something that take input values and provide outputs so they can also be treated as calculations. In Power BI DAX Calculations are of two major types: calculated measures and calculated columns.
Calculated measures – A calculated measure creates field that hold aggregated values for example average, sum, percentages, ratios etc. To create a measure, you have to go to Modeling tab in Power BI Desktop and then select ‘New Measure’. After this a formula bar will open with by default formula as ‘Measure = ‘. You can then replace the “Measure” word with the measure that you want and can also mention the expression at right side of equals to sign.
Calculated columns – This feature will add a new column. The difference between calculated column and regular column is that it is necessary to have atleast one function in calculated column. To create a column, you have to go to Modeling tab in Power BI Desktop and then select ‘New Column’. After this a formula bar will open with by default formula as ‘Column = ‘. You can replace the “Column” word with whatever column name that you want. Also, you have to add the expression you want to get calculated at other side of equals to sign.
Few Key Points about DAX Functions
- There is also a way where you can apply DAX formula on row-by-row basis.
- Any DAX Formula will always to refer to complete table or column. In other words, DAX Power BI never refers to individual cell or value. If you want to refer to a particular value then you have to apply filters in DAX Formula.
- DAX Formula can also return complete table as an output. So this output table can be used by other DAX Function as input because DAX requires complete set of values.
- DAX functions have a category known as time intelligent functions. These functions could be used to calculate time and date ranges.
Types of DAX Power BI Functions
Date and Time Functions
These functions carry out calculations on date and time values. Some of the major date and time functions are mentioned below.
- DATE
- DATEVALUE
- MONTH
- TIMEVALUE
- TODAY
- EOMONTH
- CALENDAR
- CALENDAR AUTO
Time Intelligent Functions
You can compare two time-based scenarios in your report using these functions. Basically, time intelligent functions are those which are used to calculate values over fixed time period like seconds, minutes, hours, days etc.
- DATEADD
- DATESQTD
- DATESYTD
- FIRSTDATE
- ENDOFYEAR
- NEXTQUARTER
- FIRSTNONBLANK
- DATEADD
- DATESINPERIOD
- ENDOFMONTH
Information Functions
These functions provide Information related to values selected. For a given function condition it returns TRUE or False based on the value. Some of its examples are:
- CONTAINS
- CUSTOMDATA
- ISBLANK
- ISERROR
- ISEVEN
- ISODD
- ISTEXT
- LOOKUPVALUE
- USERNAME
- ISINSCOPE
- ISERROR
- ISLOGICAL
Logical Functions
The expression or argument are evaluated logically and TRUE or False is returned from the function. Some of the logical functions are
- AND
- NOT
- IN
- IFERROR
- TRUE
- SWITCH
Mathematical and Trigonometric function
These functions are used to implement all kind of mathematical and trigonometric calculations on values. Some of the examples are
- ABS
- ATAN
- CEILING
- COMBINA
- COS
- COSH
- CURRENCY
- DEGREE
- DIVIDE
Statistical Functions
These functions help us to carry out statistics and aggregation-based calculations. Some of the examples are
- ADDCOLUMNS
- AVERGEA
- BETA.INV
- CHISQ.INV
- CONFIDENCE.NORM
- COUNTROWS
- GENERATE
We hope the article provided you with basic knowledge of DAX in Power BI. We would suggest students and freshers to go through begineers guide to learning Power BI. Additionally, we would request you to subscribe to newsletter to get such advanced topics on regular basis.