SQLServerCentral Article

DAX in Power BI Tutorial

,

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:

person.person data for the DAX in Power BI tutorial

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

Import data from SQL Server to Power BI for the DAX in Power BI tutorial.

Next, write the SQL Server Name and press OK

SQL Server connection to

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

Select Adventureworks database

In addition, select the Person.Person table and press Load. Person.person table for the DAX in Power BI tutorial

The Hello World Example

First, go to Data and select the New Column option

add a new column in Power BI

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

Hello = "hi"

Hi in DAX

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()

DAX NOW function

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()

today using DAX in Power BI

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

time=HOUR(NOW())

hour dax example

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")

today with format using DAX in Power BI

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")

time format using DAX in Power BI

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]))

concatenate using DAX in Power BI

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])

upercase in DAX

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])

Lowercase using DAX in Power BI

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")

DAX using currency

Secondly, we have a fixed format.

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

Fixed format

Thirdly, we have the Standard format.

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

standar format using DAX in Power BI

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

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

Percent using DAX in Power BI

Finally, we will show the date in Scientific format.

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

Scientific format using DAX in Power BI

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")

if in DAX

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")

IF and AND using DAX

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.

Rate

5 (1)

Share

Share

Rate

5 (1)