Power bi DAX functions with examples

Want to learn Power BI DAX? Check out this complete tutorial on power bi DAX functions with examples. We will also cover a few examples of Power bi DAX.

I recently worked on the Power BI Dax functions to generate the Power BI report, where I need to display the MIN value, Max value, and the Formatted Value in the Power BI report.

Let us now try to understand:

• Power bi DAX tutorial
• Power bi DAX functions list
• Various Power bi dax functions examples

Power BI DAX introduction

Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI Desktop, and Power Pivot in Excel.

DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

Power BI DAX is used basically to perform advanced calculations and queries on data in related tables and columns in tabular data models. DAX helps you create new information from data already in your model in Power BI.

Power BI Dax formula starts with the = (equal sign) following the equal sign we can mention the expressions and formulas.

Why do we use Dax in power bi

We can create various useful reports without using DAX in Power BI. Then the question comes why do we use DAX in Power BI or What is Dax used for in Power BI?

If you want to create a report on growth percentage or if you want to create a report based on a date range in Power BI, then in those cases Power BI DAX.

Power BI DAX will be helpful if you want to create these kinds of complex or dynamic insights from your existing data.

We will create and use DAX formulas to get the most out of your data.

Power BI DAX function lists

Let us see a few main different Dax function lists available to analyze data, and create new columns and measures in Power BI.

List of Power BI Dax functions:

1. Aggregate functions
2. Date and Time functions
3. Filter functions
4. Logical functions
5. Text functions

1. Aggregate functions – These functions calculate a scalar value such as average, minimum, maximum, sum, and count for all rows in a column or table as defined by the expression. Power BI Dax Aggregate functions are ( MIN, MAX, Average, SUM, SUMX)
2. Date and Time functions – These functions calculate the date and time value such as date, weekday, hour, current time, etc., A few Power BI Dax Date and Time functions are ( TODAY, NOW, DATE, TIME, and YEAR).
3. Filter functions – The filter function returns the filtered value in the table data. And the table contains the value that has been filtered based on the condition applied such as ( ALL, FILTERS, KEEPFILTERS, and REMOVE FILTERS).
4. Logical functions – Logical functions returns information about the values or sets in the expression such as (AND, OR, IF, SWITCH).
5. Text functions – The text function manipulates the string value such as ( REPLACE, SEARCH, UPPER, FIXED, and CONCATENATE ).

These are the few main different Dax functions lists or function references available to analyze data in Power Bi.

Read Power Query Date

DAX Data Types

In Power BI Desktop, DAX Function is used to work only with the tables. This function is having two primary data types Numeric and Non-numeric or Other.

Numeric data types contain decimals, integers, currency, etc. and Non-numeric data types or Other contains binary objects, and strings.

In the Power BI DAX function, there is another data type present known as DateTime. This DateTime data type can be used for calculation purposes of any time period. The DateTime is stored as a value with both the decimal and integer parts.

How to use Power bi DAX

Now, we will see how to use Power BI DAX. Also, we will see how to create some basic formulas and use them to create some useful Power BI report.

Well, before creating any formula in DAX, if you have knowledge of excel functions, then it will be easier for you to create DAX functions in Power BI also.

We will see here, how to create various DAX formulas and use them with our data.

Here, we will use a SharePoint list for our reporting in Power BI. You can check out an article on How to Create Power bi report from SharePoint Online list.

Here I am using the below SharePoint list.

We can create various DAX calculation formulas in the below things:

• Measures
• Calculated columns
• Calculated tables
• Row filters.

Power BI DAX Functions

To use Power BI DAX Function, the most important thing you have to learn is the formula or syntax of the language.

The below screenshot represents an example of a DAX Syntax. By using this example, you can understand properly that how you can use the DAX syntax.

Here I have explained each element of the syntax below:

• 1: It represents the name of the new measure (IncomeTaxRates2019).
• 2: It is the equal (=) operator which represents the beginning of the DAX syntax and also equating the two sides.
• 3: This SUM is the DAX function which is used to add the values of a given field (NetWageEarningsAfterTax) from the table (IncomeTaxRates).
• 4: It represents the name of the table (IncomeTaxRates) from which the column is taken in the formula.
• 5: It represents the name of the column (NetWageEarningsAfterTax) from which the formula will use the values.
• 6: This represents the multiplication (*) operator which will help to multiply two elements.

The DAX syntax is used to calculate the product of the sum of the values in NetWageEarningsAfterTax and 5.05.

After all the calculations of DAX syntax, the value will store in a new measure known as IncomeTaxRates2019.

Type of Power BI DAX Functions

In Power BI, there are some pre-defined DAX functions present which will help you to analyze your data. Every function has an argument that performs a particular operation.

The arguments in sense of it can be a number, text, column reference, logical value as TRUE or FALSE, etc. You can use one or more than one argument in a DAX formula.

In the Power BI Desktop Formula Bar, When you will start typing the DAX syntax, it will help you to show all the available functions which you want to select. You can select any function by using up and down arrow keys on your keyboard.

Let’s take an example. Suppose if you want to add a function name as SUM. So if you will type S on the formula bar, then all the functions will appear where the first letter starts on S.

In Power BI DAX, there are different types of functions present such as:

• Aggregation functions: Power BI DAX has aggregation functions which are basically used only for numeric columns.

There is a special aggregation function that ends in X as like SUMX that performs on multiple columns.

Some of the Aggregate functions are SUM, SUMX (and other X functions), MIN, MAX, AVERAGE, etc.

• Counting functions: This Counting function will help you to count some distinct values, table rows, non-empty values, etc.

Some of the counting functions are COUNT, COUNTA, COUNTBLANK, COUNTROWS, DISTINCT COUNT.

• Logical functions: Basically Logical functions are used as an operator. Suppose, if you want to perform AND operator or OR operator, then instead of writing, you can simply use && or || in the DAX formula.

Some of the Logical functions are: AND, OR, NOT, IF, IFERROR, etc.

• Information functions: In Power BI DAX, these Information functions are used situationally.

It performs on the given condition is a function for the value given and it returns as TRUE or FALSE. For instance, the function ISERROR will return TRUE if the value evaluated contains an error.

Below represents some of the Information functions as:

• CONTAINS
• CUSTOMDATA
• ISBLANK
• ISERROR
• ISEVEN
• ISINSCOPE
• ISLOGICAL
• ISNONTEXT
• ISNUMBER
• ISODD
• ISTEXT
• USERNAME, etc.

• Text functions: Power BI Text functions are very much similar to the Excel string functions. It helps to evaluate the string values.

The Power BI Text functions are:

• REPLACE
• SEARCH
• FIXED
• UPPER
• CONCATENATE
• CONCATENATEX
• EXACT
• FIND
• LEFT
• RIGHT
• LOWER
• SUBSTITUTE, etc.

• Date and Time functions: This function is the most important function in the Power BI DAX. It helps to calculate the particular date and time values and retrieve the exact date from the date values.

Always remember, the Date and Time function values are DateTime data type.

Some of the Date functions are:

• HOUR
• DATE
• NOW
• WEEKDAY
• MINUTE
• YEAR
• TODAY
• WEEKNUM
• SECOND
• TIME
• TIMEVALUE
• EOMONTH
• CALENDAR, etc.

Power bi DAX functions examples

We will see 5 examples of Power BI DAX functions.

Example-1:

Also, you may like the below Power BI articles:

• Power BI Custom Visuals
• Add Title, Image, and Video to Power BI Dashboard
• PowerBI Report Filter using Slicer Visualization
• Access to the resource is forbidden error in Power BI
• Create a Power BI report from Excel using Power BI Desktop
• Add Web Content to Power BI Dashboard
• [Video Tutorial] Create Power BI Dashboard step by step tutorial
• Microsoft Power BI Error: This content isn’t available
• Power BI On-premises Data Gateway
• Power BI Change Data Type of a Column

Conclusion

In this tutorial, we learned what is Power BI DAX and

• Why we use DAX in power bi
• DAX Data Types
• How to use Power bi DAX
• Type of Power BI DAX Functions