Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Common DAX functions and expressions for a SQL Server Tabular Database


By:   |   Last Updated: 2016-09-06   |   Comments   |   Related Tips: > Analysis Services Development

Problem

I have a Tabular Database and I'd like to know how to use some common functions and expressions used in DAX as well as how to format the display of the data?

Solution

In this tip, we will show you some common expressions in DAX using a Tabular Database as well as how to format columns.

Requirements

  1. SQL Server 2016 (most of the functions will work on SQL Server 2014, but some are exclusive for SQL Server 2016 and future versions).
  2. SSDT should be installed.
  3. A Tabular Instance installed.
  4. Some Tables with Dates, Strings and Numbers.

Introduction

Here are some typical DAX expression and function examples for a Tabular Database.

 

DISTINCTCOUNT DAX Expression

A very common function is the DISTINCTCOUNT. We can count the number of distinct rows. In this example, we will count the number of different Products using ProductKey:

Count Products:=DISTINCTCOUNT('Internet sales'[ProductKey])

DISTINCTCOUNT DAX Expression

 

SUM DAX Expression

With SUM, you can add numbers of a specified column. The following example adds the values of the TaxAmt column in the SalesOrderHeader table:

TotalTaxAmt:=SUM(SalesOrderHeader[TaxAmt])

SUM DAX Expression

 

DATEDIFF DAX Expression

DATEDIFF is a new function in SQL Server 2016. It shows the difference between two dates. In this example, we will show the number of years of an OrderDate. It shows the difference between Today and the OrderDate:

=DATEDIFF(SalesOrderHeader[OrderDate],TODAY(),YEAR)

DATEDIFF DAX Expression

 

Concatenate Strings DAX Expression

We can concatenate strings or other values using the &. The following example concatenates the FirstName and LastName with a space between them:

=[FirstName]&" "&[LastName]

Concatenate Strings DAX Expression

 

IF DAX Expression

IF allows conditional operations. The following example will show LOW if the TaxAmt is lower than 10, MEDIUM if the value is lower than 100, else HIGH:

=IF([TaxAmt]<10,"LOW",IF([TaxAmt]<100,"MEDIUM","HIGH"))

IF DAX Expression

 

SWITCH DAX Expression

IF is used for conditional operations of 2 or 3 values usually. For more values, use SWITCH. The following example shows the Region Description according to a specific numeric value. For example if Territory ID is 1, the value will be Northwest, if the value is 2 then Northeast and so on:

=SWITCH([TerritoryID],1,"Northwest",2,"Northeast",3,"Central",4,"Southwest",5,"Southeast",6,"Canada",7,"France",8,"Germany",9,"Australia",10,"United Kingdom","Other")

SWITCH DAX Expression

 

INT DAX Expression

The INT function displays the integer value of a specified number:

=INT([TaxAmt])

INT DAX Expression

 

ROUND DAX Expression

If you need to round to the closest integer value, you can use the ROUND function. The following function rounds to an integer value with 0 decimals:

=ROUND([TaxAmt],0)

ROUND DAX Expression

The following example rounds to a value with 3 decimals:

=ROUND([TaxAmt],3)

DAX ROUND example to 3 decimal places

 

Parsing Strings DAX Expression

The following example gets the lastname from the fullname. The FIND function detects the position of the space and the function returns the string starting from the space until the end of the string:

=LEFT([Full Name],(FIND(" ",[Full Name])))

Parsing Strings DAX Expression

 

DATEADD DAX Expression

The example shows how to add 2 years to a specified data using the DATEADD function:

=DATEADD(SalesOrderHeader[ShipDate],2,YEAR)

DATEADD DAX Expression

 

Formatting Column Display DAX Expression

Format Decimal Places

To format the display output there are several functions. However, the easiest way to handle the format is to go to properties. In this example, we are selecting 2 decimal places for TaxAmt:

Formatting Column Display DAX Expression

 

Format Currency Display in DAX

It is also possible to select the currency symbol using the properties. In this example, we are using British pounds as the currency symbol:

Format Currency Display in DAX

 

Change Data Format and Type in DAX

You can change the format from currency to decimal or other data types:

Change Data Format and Type in DAX

Conclusion

DAX includes functions and expressions very similar to Excel. The main idea of DAX was to create a more familiar language to create functions and expressions. DAX is simple for basic calculations, but it can be complex for some calculations.

Next Steps
For more information, refer to the following links:

Last Updated: 2016-09-06


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools