fbpx
[email protected] +1 203-349-9909 / +91-8080042523

Basics of Power BI DAX | List of Power BI DAX Function with Example

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 cloud-based 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.

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 result or output.

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. 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 Non-Numeric 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 non-numeric data type.
  • The procedure of evaluating a DAX function is that the evaluation starts from inside to outside. This makes the DAX formulating important.

Mixed datatype values can be used as inputs to the Power BI DAX formula, conversion of theses values takes place at the time of execution automatically. The output of the DAX formula will be in the datatype you instructed.


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.

Power BI DAX Syntax
Power BI DAX Syntax
  1. TotalSales defines the name of the new measure.
  2. = (equals to sign) is an operator equating two sides and starting of DAX formula.
  3. SUM is the DAX function.
  4. 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.

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.

BECOME A DATA ANALYST COURSE
• Power BI • Advanced Excel • Excel Dashboard • Excel VBA • SQL Programming Language • SAS Programming • many more

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:

    1. Click on Modelling tab in Power BI and select New Option.
    2. A formula bar will open. In that, you can replace the Column word in it by the name of your column.
    3. Enter the equation to the right-hand 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:

    1. Click on Modelling tab in Power BI and select New Measure tab.
    2. A formula bar will open. In that, you can replace the Measure word in it by the name you want.
    3. The expression which needs to be calculated can be entered on the right side of the equals to sign.
    4. 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. DAX formula allows usage 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 re-included 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.
  • 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.

Power BI DAX

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

Parent-Child Functions

The data values that are the part of parent and child hierarchy are called as parent-child 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. 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.