Introduction to DAX Language

Introduction to DAX Language

3. Please mute
your mic

4. AntoniosChatzipavlis
Data Solutions Consultant & Trainer
Since 1999
30+
Years in a Field
20+
Experience with
60+
Certifications
SQLschool.gr
Founder

5. A community for Greek professionals who use the
Microsoft Data Platform
Connect/Explore/ Learn
@antoniosch-@sqlschool
./sqlschoolgr-./groups/sqlschool
yt/c/SqlschoolGr
SQLschool.gr Group
[email protected]
Join us
Articles
SQL Saturday Nights
SQL Server in Greek
Webcasts
News
Resources

6. Connect/Explore/ Learn
V.10
2010-2020

8. Presentation Content
Introduction to
DAX
Language
•Overview
•Language Fundamentals
•Context
•Queries

9. Overview

10. •DAX is the programming language of Microsoft Power BI,
Microsoft Analysis Services, and Microsoft Power Pivot for
Excel.
-Created in 2010, with the first release of PowerPivot for Microsoft Excel
2010
•DAX has been designed to enhance data modeling,
reporting, and analytics.
•DAXis described as a functional language
-Calculations primarily use functions to generate results.
•Unlike T-SQL, there is no concept of INSERT, UPDATE, or
DELETE for manipulating data in a data model.
What is DAX?
Data Analysis eXpressions

11. A data model is a collection of data,
calculations, and formatting rules that
combine to create an object that can be
used to explore, query, and better
understand an existing dataset.
What is a Data Model?
FACT
MEASURES
DIMENSION
DIMENSION
DIMENSIONDIMENSION
DIMENSION

12. Components of a DAX Data Model
Data Tables Columns RelationshipsMeasures Hierarchies

13. Language Fundamentals

14. •DAX formulas start with the equal
sign (=) followed by an expression
•Expressions can contain functions,
operators, constants, and references
to columns
•Measure names must be enclosed in
brackets
•Table names containing spaces must
be enclosed in single quotation
marks
•Column references
-'table name'[column name]
-[column name]
DAX Syntax
CALCULATE [Total Sales]'Channel'[ChannelName]“Store”

15. Comments in DAX
= 'Channel'[ChannelPrice] * 'Channel'[ChannelDisc] -–Single line comment
= 'Channel'[ChannelPrice] -'Channel'[ChannelTax] // Single line comment
= IF ( 'Channel'[ChannelDisc] > 0 ,
/*
Multi-line
comment
*/
“Has discount”
)

16. DAX Data Types
DAX
Data Type
Power BI
Data Type
Analysis Services
Data Type
SQL Server
Data Type Stored as
Integer Whole Number Whole Number INT 64-bit integer
Decimal Decimal Number Decimal Number FLOAT 64-bit double floating
Currency Fixed Decimal NumberCurrency MONEY 64-bit integer / 10.000
DateTime DateTime, Date, TimeDate DATETIME 64-bit double floating
Boolean True/False True/False BIT Boolean
String Text Text NVARCHAR(MAX) Unicode string
Variant - - -
Binary Binary Binary VARBINARY(MAX) Binary

17. DAX Operators
Operator Type Symbol Use Example
Parenthesis ( ) Precedence order and grouping of arguments (5+2)*3
Text concatenation & Concatenation of strings “Athens” & [Country]
Arithmetic
+ Addition 4+3
- Subtraction / Negation 5-3 / -3
* Multiplication 4*3
/ Division 4/2
^ Exponents 2^6
Comparison
= Equal to [Country] = “Greece”
<> Not equal to [Country] <> “Greece”
> Greater than [Amount] > 100
>= Greater than or equal to [Amount] >= 100
< Less than [Amount] < 100
<= Less than or equal to [Amount] <= 100
Logical
&& AND condition between two Boolean expressions [Country]=“Greece” && [Amount]>0
|| OR condition between two Boolean expressions [Country]=“Greece” || [Amount]>0
IN Inclusion of an element in a list [Country] IN {“Greece”,”Cyprus”}
NOT Boolean negation NOT [Amount] > 100

18. •Variables are used to store results from DAX expressions.
•Variables can be used in any type of DAX calculation
including calculated columns, measures, and tables.
•Variables are not strictly typed and can represent any type
of object in DAX.
•Variables automatically assume the type of the object being
assigned.
•You must use a RETURN statement to close a layer of
variable scope.
•You can declare multiple variables within the same layer of
scope and you can use them with a single RETURN
statement.
•Nested variables can initialize a new layer of scope when
you use them anywhere within an expression, but all layers
of scope are ended when you use a RETURN statement.
DAX Variable
VAR varname= expression
RETURN expression
VAR myVar1 = 1
VAR myVar2 = myVar1 + 2
RETURN myVar2 * 2
Nested Measure =
VAR Level1a = 10
VAR Level1b =
VAR level2a = Level1a
VAR Leval2b = level2a * 3
RETURN Leval2b
RETURN Level1b

19. DAX Table constructors
Define anonymous tables directly in the code.
{ “Red”, “Blue”, “Green” }
Or
{ (“Red”), (“Blue”), (“Green”) }
Single Column
{
( 1,“Red” ),
( 2,“Blue” ),
( 3,“Green” )
}
Multi Column

20. DAX Conditional Statements
IF ( 20 < 30, "second", "third" ) -> "second"
IF ( 20 < 15, "second", BLANK() ) -> BLANK()
IF ( 20 < 15, "second" ) -> BLANK()
IF ( Customer[Status] = "A", "Platinum",
IF ( Customer[Status] = "B", "Gold",
IF ( Customer[Status] = "C", "Silver",
IF ( Customer[Status] = "D", "White", "None" )
)
)
)
SWITCH
(
Customer[Status],
"A", "Platinum",
"B", "Gold",
"C", "Silver",
"D", "White",
"None"
)
SWITCH
(
TRUE (),
Products[UnitPrice] < 10, "LOW",
Products[UnitPrice] < 50, "MEDIUM",
Products[UnitPrice] < 100, "HIGH",
"VERY HIGH"
)

21. Error Handling in DAX Expression
Conversion Errors
DAX automatically converts
values between strings and
numbers whenever the
operator requires it
Arithmetical
Operations Errors
DAX detects this errors and it
blocks any further
computation of the expression
and raises an error.
Empty or Missing
Values
DAX handles missing values,
blank values, or empty cells in
the same way, using the value
BLANK.

22. •BLANK value assigned to a column when
the data source contains a NULL value
•The BLANK value is automatically
converted in case it is compared with
other values
-In a numeric expression, a blank is automatically
converted into 0
-In a string expression, a blank is automatically
converted into an empty string
•ISBLANK() function checks whether a
value is blank and returns TRUE or FALSE
BLANK Function
BLANK is notthe T-SQL NULL
Sample Result
BLANK()=0 TRUE
BLANK() =“” TRUE
BLANK()-10 -10
BLANK()+18 18
5 / BLANK() INFINITY
0 / BLANK() NaN
BLANK() * 8 Blank value
BLANK() / 8 Blank value
BLANK() || BLANK() FALSE
BLANK() && BLANK() FALSE
( BLANK() = BLANK() )TRUE
( BLANK() = TRUE ) FALSE
( BLANK() = FALSE ) TRUE

23. IFERROR Function
Evaluates an expression and returns a specified value if the expression returns an error;
otherwise returns the value of the expression itself.
IFERROR ( <Value> , <ValueIfError> )
IFERROR ( A, B )
Equivalent of
IF ( ISERROR ( A ), B , A )

24. ISERROR Function
Checks whether a value is an error, and returns TRUE or FALSE.
IFERROR ( <Value> )
IF ( ISERROR ( A ), B , A )
Equivalent of
IFERROR ( A, B )

25. •Date and time functions
•Time-intelligence functions (DAX)
•Filter functions
•Information functions
•Logical functions
•Math and Trigonometry functions
•Other functions
•Parent and Child functions
•Statistical functions
•Text functions
DAX Functions
More than 200 functions in several categories

26. •DAX provides several functions
•Functions operate according to the filter context.
•Function with X suffix are suitable to aggregate the results of a more
complex expression
•Function with A suffix exist only for maintaining the Excel syntax
DAX Functions
How to use DAX Functions
<FunctionName> ( Table[Column] )
<FunctionNameX> ( Table, <expression )
<FunctionNameA> ( Table, <expression )

27. Context

28. Cardinality Cross Filter Directions
•Many to One (*:1)
•One to One (1:1)
•One to Many (1:*)
•Single
•Both
Relationships in Data Model
The Cornerstone of context
Relationships exist to join tables together so that you can work with them
as if they were one

29. Calculated Columns Measures
Calculated columns defined within tables
in the data model by adding new columns.
The expression assigned to a calculated
column is evaluated for every row of the
table
A measure is a DAX expression that is
evaluated in a context made by a set of
rows of the data model.
Context Calculations
Sales[GrossMargin] =
Sales[SalesAmount] -
Sales[TotalProductCost]
Sales[Total Sales] =
SUM ( Sales[SalesAmount] )

30. Contextisthelayeroffilteringthatis
appliedtocalculations,oftendynamically
toproducearesultspecifictoeveryvalue
inapivottableorvisual,includingrow
andcolumntotals.
Context
Row
Transition
Filter

31. Filter Context
The filter context filter

32. Row Context
The Row Context iterates

33. Row Context and Relationships
Products[NumberOrders] = COUNTROWS ( RELATEDTABLE ( Sales ) )
Products[ListAmount] = SUMX ( RELATEDTABLE ( Sales ),
Sales[OrderQuantity] * Products[ListPrice]
Sales[ListAmount] = Sales[OrderQuantity] * RELATED (Products[ListPrice])
Sales[ListAmount] = Sales[OrderQuantity] * Products[ListPrice]
RELATED Function
RELATEDTABLE Function

34. Context Transition
Context transition performed in a filter context under which it evaluates its expression.

35. Queries

36. EVALUATE Statement
DEFINE MEASURE
VAR
EVALUATE
ORDER BY
START AT

37. Relationship Queries
For SQL Developers

38. Search Queries
For SQL Developers

39. Any
questions

40. Thankyou!
@antoniosch-@sqlschool
./sqlschoolgr-./groups/sqlschool
./c/SqlschoolGr
SQLschool.gr Group
AntoniosChatzipavlis
Data Solutions Consultant & Trainer

41. A community for Greek professionals who use the Microsoft Data Platform
Copyright © SQLschool.gr. All right reserved. PRESENTER MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION