Introduction

This time we will provide an introduction to DAX in Power BI. Everybody loves Power BI. It is intuitive, easy to understand, a very cool UI. However, some people feel scared about DAX. In many reports, you can skip the use of DAX, however, it is better to know in case you need it. I created a simple introduction with simple examples for newbies. If you have some experience with Excel functions, this tutorial will be a duck soup (“chips for the parrot” as my teacher used to say).

Let’s get started.

Prerequisites

First of all, for this Power BI tutorial, we will use the Person.Person table from the Adventureworks Database (this is a SQL Server site, so I will use SQL Server data). However, you can create some data in a text if you prefer and simulate some information similar to this data:

In order to import data, go to Get Data in Power BI and select Database>SQL Server Database, and press Connect

Next, write the SQL Server Name and press OK

Also, in Adventureworks, look for the Person.person table.

In addition, select the Person.Person table and press Load.

The Hello World Example

First, go to Data and select the New Column option

Secondly, in the new column, say hi using DAX.

Hello = "hi"

Alternatively, we can say hello world

Hello = "hello world"

Time Functions

First, we will let’s start with the NOW function. This DAX code will create a column named time and show the date and time.

time = NOW()

Secondly, we will try the today function. The difference between TODAY and now is that you do not have the time and just the day whereas now returns also the time.

Today = TODAY()

Thirdly, we will get the hour from the NOW function using the hour function.

time=HOUR(NOW())

Also, you can get the minutes, years, months and days:

time = MINUTE(NOW())
time = YEAR(NOW())
time = MONTH(NOW())
time = DAY(NOW())

In addition, you can use the FORMAT function to get the date in a custom format. The following example will show how to change the format to MM/dd/yyyy.

Today with format = FORMAT(TODAY(),"MM/dd/yyyy")

The next example is formatting the date and time of the function NOW to the hh:mm:ss.

modified data with format = FORMAT(NOW(),"hh:mm:ss")

String Functions

Now, we are going to show some examples of string functions. First, we will concatenate the First name and the Last Name:

fullname = CONCATENATE('Person Person'[FirstName],CONCATENATE(" ",'Person Person'[LastName]))

You can use the table name and column or just the column.

fullname = CONCATENATE([FirstName],CONCATENATE(" ",[LastName]))

Secondly, we will use the Left function. This example shows the first letter of the Last name starting at the left.

MyColumn = LEFT('Person Person'[LastName],1)

Thirdly, we have the right function. The following example shows the first 2 letters of the last name starting at the right.

MyColumn = RIGHT('Person Person'[LastName],2)

Also, we have the UPPER function. This function will uppercase your characters.

MyColumn = UPPER('Person Person'[LastName])

In addition, we have the LOWER function to lowercase the characters. The following example shows how to lowercase the last name using the LOWER function.

MyColumn = LOWER('Person Person'[LastName])

The Format Function

Here we have the FORMAT function. You can configure the format of your numbers in DAX using this function.

Firstly, the currency FORMAT sets the numbers in currency format.

MyColumn = FORMAT( [BusinessEntityID], "Currency")

Secondly, we have a fixed format.

MyColumn = FORMAT( [BusinessEntityID], "Fixed")

Thirdly, we have the Standard format.

MyColumn = FORMAT( [BusinessEntityID], "Standard")

Also, we can use the Percent format in DAX using the introduction to DAX in Power BI.

MyColumn = FORMAT( [BusinessEntityID], "Percent")

Finally, we will show the date in Scientific format.

MyColumn = FORMAT( [BusinessEntityID], "Scientific")

IF Example Using AND

We will show how to use the IF sentence. This example checks the number of characters of the First name using the LEN function. In the length of the string is higher than 5, it will say “Long name”. Otherwise, it will say “Short name”.

MyColumn = IF(LEN([FirstName])>5,"Long Name","Short Name")

Finally, we will use the AND. If the Firstname and the Lastname length is higher than 5 characters, then it will say Long name and last name. Otherwise, it will say Short name or Short Last Name.

MyColumn = IF(AND(LEN([FirstName])>5,LEN([LasttName])>5),"Long Name and Last Name","Short Name or Short Last name")

Conclusion

To conclude, we can say that there are some simple functions in DAX similar to the functions in Excel. However, we only displayed simple examples for beginners. DAX can be very complex for queries. It is a very complete and complex language.