DAX

Data Analysis Expressions

Microsoft Reference | SQLBI | DAX Guide | DAX Formatter | DAX Do | DAX Patterns

Index	Title
1	Data Type
2	DAX Operators
3	Function Category
4	Components of DAX Data Model
5	Calculated Column, Measure and Table
6	Important Facts and Points
7	VAR & RETURN
8	Row, Filter and Query Context
9	Evaluation Order

Programming language that resembles Excel

• Power Pivot
• Power BI
• Designed for data models and business calculations.

DAX is a functional language, the execution flows with function calls.

It means, calculations mostly use functions to generate the results.

DAX is designed for enhancing Data Modeling, Reporting and Analytics capability.

In Excel we consider Cell Reference but in Power BI reference is given either to Table or to Column

SUM (
    FILTER ( 
    	VALUES ( 'Date'[Year] ), // Distinct Year
        'Date'[Year] < 2005 ),   // Year before 2005
        IF ( 
	   'Date'[Year] >= 2000, // Condition | Expression
           [Sales Amount] * 100, // If TRUE
           [Sales Amount] * 90   // If FALSE
        )
    )
)

DAX Data Types

Selection of the accurate data type helps to reduce the size of a data model and improve performance when to refresh data and use of any report.

1. Whole Number
2. Decimal Number ( floating point number )
3. Boolen ( TRUE / FALSE )
4. Text ( String )
5. Currency ( Fixed Decimal Number )
6. DateTime
7. Date
8. Time
9. String ( Unicode String )
10. Variant ( Used for expressions that returns different data type )

Variant data type is only used for Measure and in general DAX expressions.

IF ( [Age] >= 18, 1, "Not Allowed" ) // Variant 

Operators in DAX

Arithmetic Operator
Addition	+
Subtraction	-
Multiplication	*
Division	/
Exponent	^

Any argumnet passed as string is automatically converted into a number

+ : Adds two numbers, Any argument passed as a string is automatically converted into a number ( e.g. "5" + "5" = 10 )

/ : Divides Numerator with Denominator ( Return Error if Denominator is 0 )

/ Operator and DIVIDE function is different ( DIVIDE does not return error if Denominator is 0 )

Comparison Operator
Equal to	=	[City] = 'Mumbai'
Strictly equal to	==	[Price] == BLANK()
Not equal to	<>	[City] <> "Mumbai"
Greater than	>	[Age] > 18
Less than	<	[Age] < 18
Greater than or Equal to	>=	[Age] >= 18
Less than or Equal to	<=	[Age] <= 18

DAX is case insensitive, while comparing dax & DAX are equal.

= : Compares two value ( Returns TRUE if the value is BLANK() or 0 or Empty String "" )

== : Strictly Equal to ( Return TRUE only if value is actually BLANK() and FALSE if the value is 0, "" or any other value )

Logical Operator
&&	[State] = "MH" && [Country] = "IND"
||	[State] = "MH" || [Country] = "IND"
IN	[Region] IN {"AMS","APJ","EMEA"}

AND ( A, B ) or A && B ( Return TRUE only if both are TRUE and FALSE if any one is FALSE )

OR ( A, B ) or A || B ( Return TRUE if any one is TRUE and FALSE only if both are FALSE )

Text Operator
&	[City] & " " & [State] ( Concatenate )

& : Concatenates two Strings ( "Hello" & " " & "World" : Hello World )

DAX type handling

Operator Overloading : Results are based on the operators used.

e.g.

1. "5" + "4" = 9 ( Arithmetic Operation )

• Here even if we try to add numbers within quotes DAX converts string to integers and add the numbers.
• DAX knows that + is used to Add numbers.

2. 5 & 9 = 59 ( Concatenation )

• Here due to & DAX will consider Integers as string and concatenate the strings.

DAX Function Category

There are more than 200 DAX functions, there are 9 categories in DAX function.

1. Date and Time
2. Time Intelligence
3. Information
4. Logical
5. Mathematical
6. Statistical
7. Text
8. Parent / Child
9. Other

Components of DAX Data Model

• A Data Model consists of Data, Calculations and Formatting rules and it combines to create an object.
• This object helps to Explore and Understand the Dataset

1. Data
2. Tables
3. Columns
4. Relationships
5. Measures
6. Hierarchies

Calculations

There are 3 types of Calculations in DAX

1. Calculated Columns
2. Calculated Measures
3. Calculated Tables

Calculated Columns

• Column computed using a DAX language.
• Calculation happens row by row and stored in the model.
• Consumes memory in the model.

Calculated Measures

• Computes at aggregate or report level.
• Useful to calculate percentage, ratio and aggregations
• Columns cannot be directly referenced in the Measure, it will be always surrounded by some Aggregate function.
• Consumes CPU at query time.

Calculated Tables

• Creates new table or slice the subset from some existing table.
• Consumes memory in the model.

Important Terms

1. Data or Fact Table : Contains quantitative values ( cost, quantity and prices )
2. Lookup or Dimension Table : Provides descriptive attributes about each dimension.
3. Foreign Key : Contains multiple instances of each value, and are used to match the Primary keys in related Lookup tables.
4. Primary Key : Uniquely identifies each row of a table, and match Foreign keys in related Fact tables.
5. Cardinality : The uniqueness of values in the column.

Important points about Data Modeling

1. Use Star schema (One to Many) relationship.
2. Always create a relationship with one way filters.
3. Only include the data you need for analysis.
4. Split out individual Date and Time components from DateTime field.
5. Disable the refreshing of Data if that do not need refresh everytime from the Power Query Editor.

Important Facts

1. When we increase the number of columns the number of rows also increases because the combination increases.
2. The limit of excel is Million rows.
3. When then limit exceeds we need Power Pivot or Power BI

Statements VAR & RETURN

• The VAR keyword introduces variables in an expression.
• Variables make the calculation easier to understand.
• Writing any complex or nested expression using DAX functions, variables can help to break these complex calculations into smaller, more useful sections.
• Reduce complexity, Easy to Debug, Improve readability and Improve performance
• The results ( defined value or evaluated expressions ) of VAR statement is returned by RETURN statement.

VAR Pi = 3.14                                                         // Defined 

VAR AreaOfCircle = SUMX ( Math, Pi * Math[Radius] * Math[Radius] )    // Expression

RETURN AreaOfCircle

• The RETURN keyword consumes variables defined in previous VAR statements.
• It access to all expressions and results defined in the VAR statements before or above RETURN.

VAR Name = "Kirankumar"

RETURN Name

Context in DAX

• Context is how DAX apply layers of filters to calculations and tables.
• Used in the calculations so that they return relevant results for every value.
• Produce a result related to each and every value of a visual or pivot table including rows and columns total.

1. Row Context

• Row Context is related to current rows
• If you create a Calculated Column, the Row Context involves the values of all the Columns ( entire Row )
• If that table has a relationship with other table, then it includes all the related values from that Table for that Row
• In Iterative functions in DAX over table, each Row has its own Row Context

2. Filter Context

• Applying Filters on set of values of Columns or Tables using DAX calculations.
• Filter Context applies on the top of Row Context and Query Context
• Move from One side to Many side.
• e.g. Filter on category will automatically filter the sub categories and further it will filter the products.

1. Rows or Columns
2. By Slicer
3. Through Filter Pane
4. To the Calculated Measure

3. Query Context

• Combination of Row Context and Filter Context creates a final query for DAX
• Users explicitly mention Row Context and Filter Context for DAX
• DAX implicitly creates the Query Context from that Row Context and Filter Context

Evaluation Order

1. Individual functions : Left to Right ( Startting from first parameter and following the order )

IF(Logical, Return IF True, Return IF False)

2. Nested functions : Inside Out ( Start from innermost function and work outward )

= SUMX (
       FILTER (
              RELATED ( )
       )
  )

Error Handling

Helps us to identify missing data. ( Quality assurance and testing )

1. IFERROR() : IFERROR(Value, ValueIfError)

Error Check = 
IFERROR (
        1/0,
        BLANK()
)        

2. ISBLANK() : ISBLANK(Value)

IF (
   ISBLANK (
           [Sales (Last Year)]
   ),
   "No Sales",
   [Sales (Last Year)]
)