Give you a preview of advance excel techniqueDescrição completa
Give you a preview of advance excel techniqueFull description
Give you a preview of advance excel technique
tutor vba excelFull description
Descripción: Give you a preview of advance excel technique
tutorialFull description
Funciones DAX
Descripción completa
Excel-vba-programming-tutorial-pdf
Tutorial sobre como utilizar o AutoCAD junto com o Excel por meio de programação VBA.Deskripsi lengkap
DAX
About the Tutorial DAX (Data Analysis Expressions) is a formula language that helps you create new information from the data that already exists in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making. It is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Excel Power Pivot.
Audience This tutorial has been designed for all those readers who depend heavily on MS-Excel to prepare charts, tables, and professional reports that involve complex data. It will help all those readers who use MS-Excel regularly to analyze data. Professionals who use data modeling and data analysis for reporting and decision-making purposes will benefit from this.
Prerequisites This tutorial is an extension to Excel Power Pivot tutorial, hence it is a good idea to brush up on the Excel Power Pivot tutorial before you delve into DAX. Knowledge of Excel Functions and Excel Formulas is not necessary for this tutorial, as DAX is entirely for the Data Model in the Power Pivot window.
Disclaimer & Copyright Copyright
2017 by Tutorials Point (I) Pvt. Ltd.
All the content and graphics published i n this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as preci sely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at [email protected]. at [email protected].
i
DAX
Table of Contents About the Tutorial.................................................................................................................................... i Audience .................................................................................................................................................. i Prerequisites ............................................................................................................................................ i Disclaimer & Copyright............................................................................................................................. i Table of Contents .................................................................................................................................... ii
Understanding Calculated Columns......................................................................................................... 6 Creating a Calculated Column ................................................................................................................. 7 Renaming the Calculated Column.......................................................................................................... 10 Checking the Data Type of the Calculated Column ................................................................................ 11 Errors in Calculated Columns ................................................................................................................. 11
Understanding Calculated Fields ........................................................................................................... 13 Calculated Field – An Example ............................................................................................................... 14 Creating a Calculated Field in a Table .................................................................................................... 14 Using the Calculated Field in a Power PivotTable .................................................................................. 16 Types of Calculated Fields ..................................................................................................................... 18 Creating an Implicit Calculated Field ..................................................................................................... 18 Drawbacks of an Implicit Calculated Field ............................................................................................. 21 Creating an Explicit Calculated Field ...................................................................................................... 21
Finding Calculated Fields ....................................................................................................................... 24 Viewing Calculated Fields in t he Table .................................................................................................. 26 Changing a Calculated Field in the Table ............................................................................................... 27 Renaming a Calculated Field in the Data Model .................................................................................... 27 Viewing Calculated Fields in the Excel Window ..................................................................................... 29 Changing a Calculated Field in the Manage Calculated Fields ................................................................ 30 Renaming a Calculated Field in the Manage Calculated Fields ............................................................... 31 Moving a Calculated Field in the Data Model ........................................................................................ 32
Deleting an Explicit Calculated Field in the Data Model......................................................................... 33 Deleting an Implicit Calculated Field ..................................................................................................... 38
Differences between Excel Formulas and DAX Formulas ....................................................................... 40 Syntax for DAX Formulas ....................................................................................................................... 41 DAX Functions ....................................................................................................................................... 42
iii
DAX
DAX Table Naming Requirements ......................................................................................................... 42 DAX Fully Qualified Names – Special Cases ........................................................................................... 44 Exclusion of Special Characters in Names .............................................................................................. 44 Common DAX Formula Rules................................................................................................................. 44 DAX Special Values ................................................................................................................................ 45 DAX Operators ...................................................................................................................................... 45 New Data Types in DAX ......................................................................................................................... 46 Summary of DAX Data Types ................................................................................................................. 46 Implicit Data Type Conversion in DAX Formulas .................................................................................... 48 How DAX Handles Blanks, Empty Strings, and Zero Values? .................................................................. 50
Types of DAX Operators ........................................................................................................................ 52 DAX Operator Precedence Order........................................................................................................... 54 DAX Expression Syntax .......................................................................................................................... 55 Using Parentheses to Control DAX Calculation Order ............................................................................ 56 Differences Between Excel and DAX ...................................................................................................... 56
Standard Parameter Names .................................................................................................................. 58 Prefixing Parameter Names................................................................................................................... 59 Using Only the Prefix as a Parameter .................................................................................................... 59
What is a DAX Function? ....................................................................................................................... 60 Excel Functions vs. DAX Functions ......................................................................................................... 60 Types of DAX Functions ......................................................................................................................... 61
DAX Function – Explanation Structure................................................................................................... 71
iv
DAX
Types of Context in DAX ........................................................................................................................ 73
DAX Functions vs. DAX Formulas ........................................................................................................... 76 Understanding DAX Formula Syntax ..................................................................................................... 76 Understanding IntelliSense Feature ...................................................................................................... 77 Where to Use DAX Formulas? ............................................................................................................... 77 Creating a DAX Formula ........................................................................................................................ 78 Tips for Using AutoComplete................................................................................................................. 80 Understanding Insert Function Feature ................................................................................................. 80 Using Insert Function in a DAX Formula ................................................................................................ 81 Using Multiple Functions in a DAX Formula........................................................................................... 83 Creating a DAX Formula Using Standard Aggregations .......................................................................... 84 DAX Formulas and the Relational Model ............................................................................................... 86 Referential Integrity .............................................................................................................................. 87
Understanding Data Refresh vs. Recalculation ...................................................................................... 88 Different Ways to Update Data in Data Model ...................................................................................... 88 Recalculation of DAX Formulas ............................................................................................................. 89
Different Ways of Updating Data in the Data Model ............................................................................. 90 Refreshing Data in the Data Model ....................................................................................................... 90 Manually Refreshing an Existing Data Source ........................................................................................ 91 Changing a Data Source ......................................................................................................................... 93 Modifying a Connection to an Existing Data Source .............................................................................. 94 Editing Table and Column Mappings (Bindings) ..................................................................................... 96
v
DAX
Changing a Column Name and Data Type .............................................................................................. 98 Adding / Changing a Filter to a Data Source ........................................................................................ 100
Types of Recalculation ........................................................................................................................ 104 Recalculating DAX Formulas Automatically ......................................................................................... 104 When to Use Manual Recalculation Mode?......................................................................................... 105 Configuring the Workbook for Manual Recalculation .......................................................................... 105 Recalculating DAX Formulas Manually ................................................................................................ 106
Dependencies...................................................................................................................................... 107 Sequence of Recalculation for Dependent Columns ............................................................................ 107 Transactions ........................................................................................................................................ 107 Recalculation of Volatile Functions ..................................................................................................... 108
DAX Error: Calculation Aborted ........................................................................................................... 109 DAX Semantic Error - An Example ....................................................................................................... 110
Why Time Intelligence Makes DAX Powerful? ..................................................................................... 111 Requirements for DAX Time Intelligence Functions ............................................................................. 111 DAX Time Intelligence Functions – Categories ..................................................................................... 112
Performing Complex Calculations........................................................................................................ 122 Working with Text and Dates .............................................................................................................. 122 Conditional Values and Testing for Errors ........................................................................................... 123
vi
DAX
Using Time Intelligence ....................................................................................................................... 123 Ranking and Comparing Values ........................................................................................................... 123
Creating Custom Calculations for a PivotTable .................................................................................... 124 Filtering Data in Formulas ................................................................................................................... 126
Creating a Key Column by Concatenation............................................................................................ 129 Date Based on Date Parts Extracted from a Text Date ......................................................................... 129 Defining a Custom Date Format .......................................................................................................... 130 Changing Data Types of DAX Formula Outputs .................................................................................... 133
Creating a Value Based on a Condition ................................................................................................ 135 Testing for Errors within a DAX Formula ............................................................................................. 135
Calculating Cumulative Sales ............................................................................................................... 136 Comparing Values across Different Time Periods ................................................................................ 137 Comparing Values Across Parallel Time Periods .................................................................................. 138 Calculating Running Totals .................................................................................................................. 138 Calculating a Value over a Custom Date Range ................................................................................... 139
Applying a Filter to Show only the Top Few Items ............................................................................... 141 Advantages and Disadvantages of Applying Filter ............................................................................... 142 Creating a DAX Formula That Dynamically Ranks Values ..................................................................... 142 Advantages and Disadvantages of Dynamic Ranks .............................................................................. 143
vii
1. DAX ─ Overview
DAX
DAX stands for Data Analysis Expressions. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Excel Power Pivot. It is not a programming language, but is a formula language that allows the users to define custom calculations in calculated columns and calculated fields (also known as measures). DAX helps you create new information from the data that is already present in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making. DAX includes some of the functions that are used in Excel formulas, but with modified functionality and additional functions that are designed to work with relational data and perform dynamic aggregation.
Importance of DAX The foundation of DAX is the Data Model that is the Power Pivot database in Excel. Data Model consists of tables between which relationships can be defined so as to combine the data from different sources. The data connections to the Data Model can be refreshed as and when the source data changes. Data Model makes the use of the Power Pivot xVelocity in-memory analytics engine (VertiPaq) that makes the data operations to be as quick as possible in addition to accommodating several thousands of rows of data. For more information on Data Model, refer to the tutorial – Power Pivot. DAX in conjunction with Data Model enables several power features in Excel – Power Pivot, Power PivotTables, Power PivotCharts and Power View. You can use DAX to solve a number of basic calculations and data analysis problems. DAX is also useful in Power BI to create a new Power BI Desktop file and import some data into it. Further, DAX formulas provide capabilities such as analyzing growth percentage across product categories and for different date ranges, calculating year-over-y ear growth compared to market trends and many others as well. Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line. This is the power in Power BI and DAX will help you get there.
Prerequisites for this Tutorial This tutorial is an extension to Excel Power Pivot tutorial, where you have learnt about the Power Pivot feature, Data Model, Relationships, Power PivotTables, Power Pivot Charts, etc. It would be a good idea to brush up on this tutorial before you delve into DAX as this tutorial is more on the DAX language wherein you write formulas for the analysis of data in the Data Model and report those results.
1
DAX
This tutorial also introduces DAX Functions that are like Excel Functions, but with some variations. A comparison of Excel Functions and DAX Functions is provided to help you distinguish both. Similarly, Excel formulas and DAX formulas are compared and the similarities and differences are discussed. A good understanding of these differences would help you in writing effective DAX formulas efficiently. Knowledge of Excel Functions and Excel Formulas is not necessary for thi s tutorial, as DAX is entirely for the Data Model in the Power Pivot window. You will get into an Excel worksheet only to view the Power PivotTables, Power Pivot Charts and Power View visualizations that are based on Data Model. However, if you are an Excel professional with good amount of knowledge in Excel Functions and Formulas, better make a note of what is mentioned in the previous section and the details given in the course of this tutorial.
Calculated Columns Calculated columns are the columns that you can add to a table in the Data Model, by means of a DAX formula. You have already learnt about them in Excel Power Pivot tutorial, but you will learn in detail in the chapter – Calculated Columns as DAX is all about calculated columns, calculated fields, and DAX functions.
Calculated Fields / Measures You cannot change the values in the tables in the Data Model by editing. However, you can add calculated fields to a table that can be used in the Power PivotTables. The calculated fields are defined by gi ving a name and by defining a DAX formula. For details, refer to the chapter – Calculated Fields. The calculated fields were named as measures in the Excel versions prior to Excel 2013. They are renamed back to measures in Excel 2016. In this tutorial, we will refer them as calculated fields. But, note that the terms - calculated fields and measures - are synonymous and refer to the same in all aspects. You can edit a calculated field after it is defined and stored. You can change the DAX formula used in the definition or you can rename the calculated field. You will learn about this in the chapter – Editing a Calculated Field. You can delete a calculated field. Refer to the chapter – Deleting a Calculated Field.
DAX Formulas DAX formulas form the heart of the DAX language. You can create calculated fields and calculated columns by defining them with DAX formulas. You can write DAX formulas for the data analysis operations. DAX formulas do not refer to the individual cells or range of cells in the table, but refer to the tables and columns in the Data Model. A column in a table in the Data Model must contain the same data type. DAX formulas contain the tables, columns, calculated columns, calculated fields, DAX operators, and DAX functions. Refer to the chapter – DAX Formulas to learn in detail.
2
DAX
DAX Syntax As is the case with any language, DAX, the formula language also has a syntax. Your DAX formulas should follow DAX syntax, or else, you will either get errors at design time or at run time or you will receive incorrect results. You will learn the following in the chapter – DAX Syntax:
DAX naming requirements for Tables, Columns
DAX operators
DAX special values
DAX data types
DAX implicit data type conversions
DAX Operators DAX is a formula language and hence makes the use of the operators in defining the formulas. DAX has the following types of operators –
DAX Arithmetic Operators
DAX Comparison Operators
DAX Text Concatenation Operator
DAX Logical Operators
DAX operator precedence order is also defined and varies from Excel operator preced ence order. Refer to the chapter – DAX Operators.
DAX Standard Parameters DAX Function syntax has certain requirements on parameters. This is because the DAX function arguments can be tables or columns or calculated fields or other DAX functions. Refer to the chapter - DAX Standard Parameters.
DAX Functions Excel 2013 has 246 DAX functions that you can use in DAX formulas. You will learn about these functions at the category level in the chapter – DAX Functions. However, for details on each DAX function syntax, parameters, usage and return values, you have to refer to our tutorial on – DAX Functions. The section names used for the description of each DAX function is given in the chapter – Understanding DAX Functions. As DAX functions are required in writing the DAX formulas and the results of the DAX functions used depend on the context they are used, you might have to go back and forth between these two tutorials to get a grasp on DAX that you will use in Data Modeling with DAX and Power BI.
3
DAX
DAX Special Functions DAX has some functions that make DAX powerful. These DAX functions come under the categories – DAX time intelligence functions and DAX filter functions and require a speci al mention. You will learn about DAX time intelligence functions in the chapter – Understanding DAX Time Intelligence. You will learn about the usage of DAX filter functions in the chapter – DAX Filter Functions.
DAX Evaluation Context The results of a DAX formula can vary based on the context that is used for evaluation. DAX has two types of evaluation context – Row Context and Filter Context. Refer to the chapter - DAX Evaluation Context.
DAX Formulas DAX is a formula language and you have to get the most of it in writing the DAX formulas. Refer to the chapter - DAX Formulas to learn about the formula syntax and how to create them easily and correctly. The results of the DAX formulas change whenever the data is refreshed and whenever the DAX formulas are recalculated. You have to understand the difference between data refresh and recalculation. Refer to the chapter - Updating the Results of DAX Formulas. Data in the Data Model is expected and subjected to change from time to time. This is because the data is used for data analysis activities that require up-to-date data at any point of time. To understand the different ways of refreshing data, refer to the chapter Updating Data in Data Model. You will understand the different types of DAX formula recalculation in the chapter Recalculating DAX Formulas. DAX formula recalculations have to consider data dependencies and follow a specific order. Otherwise, you might get errors or erroneous results. Refer to the chapter Troubleshooting DAX Formula Recalculation for details. You will get an insight into some of the common DAX formula errors and you will learn how to fix those errors, in the chapter - DAX Formula Errors.
DAX Scenarios If you start learning a new language, the best way of getting acquainted to the language is by understanding where to use what. Similarly, DAX being a formula language meant for data analysis, you need to understand the various scenarios where it can be used. Refer to the following chapters to get details on this.
DAX Scenarios
Scenarios - Performing Complex Calculations
Scenarios - Working with Text and Dates
Scenarios - Conditional Values and Testing for Errors
4
DAX
Scenarios - Using Time Intelligence
Scenarios - Ranking and Comparing Values
5
2. DAX ─ Calculated Columns
DAX
A calculated column is a column that you add to an existing table in the Data Model of your workbook by means of a DAX formula that defines the column values. Instead of importing the values in the column, you create the calculated column. You can use the calculated column in a PivotTable, PivotChart, Power PivotTable, Power PivotChart or Power View report just like any other table column.
Understanding Calculated Columns The DAX formula used to create a calculated column is like an Excel formula. However, in DAX formula, you cannot create different formulas for different rows in a table. The DAX formula is automatically applied to the entire column. For example, you can create one calculated column to extract Year from the existing column – Date, with the DAX formula – =YEAR ([Date])
YEAR is a DAX function and Date is an existing column in the table. As seen, the table name is enclosed in brackets. You will learn more about this in the chapter – DAX Syntax. When you add a column to a table with this DAX formula, the column values are computed as soon as you create the formula. A new column with the header CalculatedColumn1 filled with Year values will get created. Column values are recalculated as necessary, such as when the underlying data is refreshed. You can create calculated columns based on existing columns, calculated fields (measures), and other calculated columns.
6
DAX
Creating a Calculated Column Consider the Data Model with the Olympics Results as shown in the following screenshot.
Click the Data View.
Click the Results tab.
You will be viewing the Results table.
7
DAX
As seen in the above screenshot, the rightmost column has the header – Add Column.
Click the Design tab on the Ribbon.
Click Add in the Columns group.
The pointer will appear in the formula bar. That means you are adding a column with a DAX formula.
Type =YEAR ([Date]) in the formula bar.
8
DAX
As can be seen in the above screenshot, the rightmost column with the header – Add Column is highlighted.
Press Enter.
It will take a while (few seconds) for the calculations to be done. Please wait. The new calculated column will get inserted to the left of the rightmost Add Column.
As shown in the above screenshot, the newly inserted calculated column is highlighted. Values in the entire column appear as per the DAX formula used. The column header is CalculatedColumn1.
9
DAX
Renaming the Calculated Column To rename the calculated column to a meaningful name, do the following –
Double-click on the column header. The column name will be highlighted.
Select the column name.
Type Year (the new name).
As seen in the above screenshot, the name of the calculated column got changed.
10
DAX
You can also rename a calculated column by right-clicking on th e column and then clicking on Rename in the dropdown list. Just make sure that the new name does not conflict with an existing name in the table.
Checking the Data Type of the Calculated Column You can check the data type of the calculated column as follows –
Click the Home tab on the Ribbon.
Click the Data Type.
As you can see in the above screenshot, the dropdown list has the possible data types for the columns. In this example, the default (Auto) data type, i.e. the Whole Number is selected.
Errors in Calculated Columns Errors can occur in the calculated columns for the following reasons
Changing or deleting relationships between the tables. This is because the formulas that use columns in those tables will become invalid.
The formula contains a circular or self-referencing dependency.
11
DAX
Performance Issues As seen earlier in the example of Olympics results, the Results table has about 35000 r ows of data. Hence, when you created a column with a DAX formula, it had calculated all the 35000+ values in the column at once, for which it took a little while. The Data Model and the tables are meant to handle millions of rows of data. Hence, it can affect the performance when the DAX formula has too many references. You can avoid the performance issues doing the following –
If your DAX formula contains many complex dependencies, then create it in steps saving the results in new calculated columns, instead of creating a single big formula at once. This enables you to validate the results and assess the performance.
Calculated columns need to be recalculated when d ata modifications occur. You can set the recalculation mode to manual, thus saving frequent recalculations. However, if any values in the calculated column are incorrect, the column will be grayed out, until you refresh and recalculate the data.
12
3. DAX ─ Calculated Fields / Measures
DAX
A calculated field in a table in a Data Model is the field obtained by a DAX formula. In earlier versions of Power Pivot, the calculated field was termed as a measure. In Excel 2013, it was renamed as a calculated field. However, it is renamed back to measure in Excel 2016. If you refer to any documentation, you can observe a mix up of these two terms. Note that the terms calculated field and measure are synonymous. In this tutorial, we use the term calculated field.
Understanding Calculated Fields A calculated field is a formula that is created specifically for use in a PivotTable (or PivotChart). You can create a calculated field based on standard aggr egation functions, such as COUNT or SUM, or by defining your own DAX formula. Following is the difference between the calculated field and the calculated column
A calculated field can be used only in the VALUES ar ea of a PivotTable.
A calculated column with the calculated results can be used in ROWS, COLUMNS and FILTERS areas also.
Saving Calculated Field The calculated field will be saved with its source table in the Data Model. It appears in the Power PivotTable or Power PivotChart Fields list as a field in the table.
Using Calculated Field To use a calculated field, you have to select it from the Power PivotTable Fields list. The calculated field will get added to the VALUES area and the formula us ed for the calculated field will be evaluated. A result is created for each combination of row and column fields.
13
DAX
Calculated Field – An Example Consider the following Data Model for Olympics data –
As seen in the above screenshot, the Results table has a field Medal that contains the values – Gold, Silver, or Bronze for each of the r ows containing Sport – Event - Country – Date combination. Suppose you want medal count for each country, then you can create a calculated field Medal Count with the following DAX formula – Medal Count := COUNTA([Medal])
Creating a Calculated Field in a Table To create the calculated field Medal Count in the Results table, do the following
Click the cell in the calculation area below the Medal column in the Results table. The cell will be highlighted.
Type Medal Count:=COUNTA([Medal]) in the formula bar.
14
DAX
Press Enter.
As seen in the above screenshot, the calculated field appears in the selected cell, showing the value as 34,094. This number is the total number of rows in the Results table. Hence, it does not make much sense at the first look. As discussed earlier, the real use of a calculated field can be seen only by adding it to a Power PivotTable or a Power PivotChart.
15
DAX
Using the Calculated Field in a Power PivotTable To use the calculated field to count the number of medals for each country, do the following:
Click the PivotTable on the Ribbon in the Power Pivot window.
Click the PivotTable in the dropdown list.
Create PivotTable dialog box appears.
Click the Existing Worksheet.
Select where you want to place the PivotTable.
An empty PivotTable will get created.
Click the Results table in the PivotTable Fields list.
Click the fields – Country and Medal count.
16
DAX
As you can observe, Medal Count is added to VALUES area and Country i s added to ROWS area. The PivotTable is created with the field Country values appearing in the rows. And for each row, the Medal Count value is calculated and displayed. That is the way, the calculated field evaluates the DAX formula used and displays the values.
Add the field Sport from the Results table to ROWS area.
As you can see in the above screenshot, Medal Count is calculated for each Country Sport-wise and a Subtotal for the Country itself. This is how DAX supplements the Power features.
17
DAX
Types of Calculated Fields There are two types of Calculated Fields – Implicit and Explicit.
An implicit calculated field is created in the Power PivotTable Fields list pane.
An explicit calculated field is created either in the table in the Power Pivot window, or from the PowerPivot Ribbon in the Excel window.
Creating an Implicit Calculated Field An implicit calculated field can be cr eated in two ways, both in the Power PivotTable Fields pane.
Creating an Implicit Calculated Field in the PivotTable Fields List You can create the Count of Medal Field from the Medal field in the PivotTable Fields list as follows –
Deselect the field Medal Count.
Right-click on the field Medal.
Click Add to Values in the dropdown list.
Count of Medal appears in the Values area. Count of Medal column will be added to the PivotTable.
18
DAX
Creating an Implicit Calculated Field in the VALUES Area You can create an implicit calculated field - % of Parent Row in the Values area to express the Medal count of each sport that a country has won as a percentage of the total number of Medals won by that Country.
Click the down arrow in the Count of Medal box in VALUES area.
Click the Value Field Settings in the dropdown list.
19
DAX
Value Field Settings dialog box appears.
Type % Medals in the Custom Name box.
Click the Show Values As tab.
Click the box under Show values as.
Click the % of Parent Row Total.
Click the Number Format button.
Format Cells dialog box appears.
Click Percentage.
Type 0 in decimal places.
Click OK.
Click OK in the Value Field Settings dialog box.
Select Do Not Show Subtotals.
20
DAX
You created another implicit calculated field % Medals and as you can observe, for each Country, the percentage of Medals Sport-wise are displayed.
Drawbacks of an Implicit Calculated Field Implicit calculated fields are easy to create. In fact, you have been creating them even in Excel PivotTables and Pivot Charts. But, they have the following drawbacks –
They are volatile. That means, if you deselect the field you used for calculated field, it will be removed. If you want to display it again, you have to once again create it.
Their scope is limited to the PivotTable or PivotChart in which they are created. If you create another PivotTable in another worksheet, you have to create the calculated field again.
On the other hand, explicit calculated fields will get saved with the table and will be available whenever you select that table.
Creating an Explicit Calculated Field You can create an explicit calculated field in two ways –
In the calculation area in a Table in the Data Model. You have already learnt this in the section – Creating Calculated Field in a Table.
From PowerPivot Ribbon in the Excel table. You will learn this way of creating an explicit calculated field in the next section.
21
DAX
Creating an Explicit Calculated Field from PowerPivot Ribbon To create an explicit calculated field from PowerPivot Ribbon, do the following –
Click the POWERPIVOT tab on the Ribbon in your workbook.
Click the Calculated Fields in the Calculations area.
Click the New Calculated Field in the dropdown list.
Calculated Field dialog box appears.
Fill in the required information as shown in the following screenshot.
22
DAX
Click the Check formula button.
Click OK only if there are no errors in the formula.
As you can observe, you can define the category and format of the calculated field in this dialog box. Further, you can use the IntelliSense feature to understand the usage of the functions and to use the AutoComplete feature to easily complete the names of the functions, tables, and columns. For details on IntelliSense feature, refer to the chapter – DAX Formulas. This is a recommended way to create explicit calculated fields.
23
4. DAX ─ Editing a Calculated Field
DAX
You can edit a calculated field to modify it. But, before you edit a calculated field, you should know where it is stored. That means, in which table the calculated field is stored. This holds both for implicit and explicit calculated fields. A calculated field can be associated with only one table in a Data Model.
Finding Calculated Fields To find the calculated fields in the Data Model, do the following –
Click the Advanced tab on the Ribbon in the Power Pivot window.
Click the Show Implicit Calculated Fields.
Click the Diagram View.
24
DAX
As you can see in the above screenshot, Show Implicit Calculated Fields is highlighted on the Ribbon. If it is not highlighted, click it again. You can also observe that there are 4 checkboxes – Columns, Calculated Fields, Hierarchies, and KPIs. By default, all the 4 are selected.
Uncheck the boxes - Columns, Hierarchies and KPIs.
This will leave only Calculated Fields box checked.
As seen in the above screenshot, only the Results table has fields displayed. The other two tables are blank. This shows that only th e Results table has calculated fields. You can also observe that the implicit calculated fields have an icon calculated field – Medal Count does not have that icon.
displayed, whereas the explicit
25
DAX
Viewing Calculated Fields in the Table You can view the calculated fields in the Table as follows
Click the calculated field.
Right-click and select Go To in the dropdown list.
The table will appear in Data View.
As seen in the above screenshot, the calculated fields appear in the calculation area of the table.
26
DAX
Changing a Calculated Field in the Table You can change the formula used for a calculated field in the table.
Click the calculated field in the table in data view of the Data Model.
Select the formula in the formula bar – to the right side of :=.
The formula will get highlighted.
Type the new formula.
Press Enter.
You will learn more about the DAX formulas in the subsequent chapters.
Renaming a Calculated Field in the Data Model You can change the name of a calculated field in the Data Table either in Data View or Diagram View.
Renaming a Calculated Field in the Data View
Click the calculated field in the table in data view of the Data Model.
Select the calculated field name in the formula bar – to the left side of :=.
The calculated field name will get highlighted.
27
DAX
Type the new name for the calculated field.
Press Enter.
You will learn more about the DAX Syntax in the subsequent chapters.
Renaming a Calculated Field in the Diagram View
Right-click the calculated field name in the table in the diagram view.
Click Rename in the dropdown list.
The name will get into editing mode. Type the new name for the c alculated field.
28
DAX
Viewing Calculated Fields in the Excel Window You can view the calculated fields in the Excel window as follows
Click the POWERPIVOT tab on the Ribbon.
Click Calculated Fields in the Calculations group.
Click Manage Calculated Fields in the dropdown list.
Manage Calculated Fields dialog box appears. The names of the explicit calculated fields in the Data Model appear in the dialog box.
29
DAX
Changing a Calculated Field in the Manage Calculated Fields You can change a calculated field in the Manage Calculated Fields dialog box.
Click the Medal Count.
Click the Edit button.
Calculated Field dialog box appears.
Select the formula to the right of = in the formula box.
30
DAX
Type the new Formula.
Click OK.
Click Close in the Manage Calculated Fields dialog box.
Renaming a Calculated Field in the Manage Calculated Fields You can rename a calculated field in the Manage Calculated Fields dialog box.
Click the Medal Count.
Click the Edit button.
Calculated Field dialog box appears.
Select the name in the calculated field name box.
31
DAX
Type the new name for the calculated field.
Click OK.
Click Close in the Manage Calculated Fields dialog box.
Moving a Calculated Field in the Data Model You can move a calculated field within the calculation area of the table in which it is created. But, it cannot be moved to another table.
Right-click the calculated field.
Click Cut.
Move the pointer to a different place in the calculation area of the same table.
Click Paste.
Note: It does not really matter where the calcul ated field is within the calculation area of the table because the data references in the DAX formula of the calculated field are by the column names and are stated explicitly.
32
5. DAX ─ Deleting a Calculated Field
DAX
You can delete both explicit and implicit calculated fields. There are se veral ways of doing so, which you will learn in this chapter. However, you need to remember the following poin ts before deleting a calculated field –
An explicit calculated field can be used in more than one PivotTable and/or PivotChart. Hence, you need to make sure that deleting the explicit calculated field does not affect any of the reports that you have already generated.
An explicit calculated field can be used in the calculations of other explicit calculated fields. Hence, you need to make sure that the explicit calculated field is not used in any of the calculations of other explicit calculated fields.
An implicit calculated field is limited to the PivotTable or PivotChart where it is us ed. Hence, before deleting an implicit calculated field, it is just sufficient to make sure that it can be deleted from the corresponding PivotTable or PivotChart.
Creating an implicit calculated field is simpler than creating an explicit calculated field. Hence, more caution is required before deleting an explicit calculated field.
You cannot create an explicit calculated field, if the name conflicts with the name of an implicit calculated field. Hence, you might have to delete that implicit calculated field before creating the explicit calculated field.
Deleting an Explicit Calculated Field in the Data Model You can delete an explicit calculated field either in the data view or the diagram view in the Data Model.
Deleting an Explicit Calculated Field in the Data View
Locate the calculated field in the calculations area in the Data View.
Right-click the calculated field.
Click Delete in the dropdown list.
33
DAX
Message appears for delete confirmation.
Click Delete from Model. The explicit calculated field will get deleted.
Deleting an Explicit Calculated Field in the Diagram View
Locate the calculated field in the data table in the Diagram View.
Right-click the calculated field name.
Click Delete in the dropdown list.
34
DAX
Message appears for delete confirmation.
Click Delete from Model. The explicit calculated field will get deleted and it will not be seen in the fields list of the data table.
Deleting an Explicit Calculated Field in the Excel Window You can delete an explicit calculated field from the Excel window as follows –
Click the POWERPIVOT tab on the Ribbon.
Click Calculated Field in the Calculations group.
Click Manage Calculated Fields in the dropdown list.
35
DAX
Manage Calculated Fields dialog box appears.
Click the explicit calculated field name.
Click the Delete button.
Confirmation message for deletion appears.
36
DAX
Click Yes. Information message that the Data Model is changed appears at the top. Click the Close button in the dialog box.
The explicit calculated field will get deleted and it will not be seen in the PivotTable/PivotChart Fields list in the workbook.
37
DAX
Deleting an Implicit Calculated Field You can delete an implicit calculated field either in the data view or the diagram view in the Data Model.
Deleting an Implicit Calculated Field in the Data View
Locate the calculated field in the calculations area in the Data View.
Right-click the calculated field.
Click Delete in the dropdown list.
Message appears for delete confirmation.
Click Delete from Model. The implicit calculated field will get deleted.
Deleting an Implicit Calculated Field in the Diagram View
Locate the calculated field in the data table in the Diagram View.
Right-click the calculated field name.
Click Delete in the dropdown list. 38
DAX
Message appears for delete confirmation.
Click Delete from Model. The implicit calculated field will get deleted and it will not be seen in the fields list of the data table.
39
6. DAX ─ Syntax
DAX
As discussed earlier, DAX is i s a formula language comprising of operators, values, functions, and formulas. In this chapter, you will learn about DAX Syntax. DAX Syntax can Syntax can be categorized as –
Syntax for DAX Formulas
DAX Naming Requirements
DAX Special Values
DAX Functions
DAX Operators
DAX Data Types
Before you proceed to learning DAX Syntax, you have to understand the difference between Excel formulas and DAX formulas.
Differences between Excel Formulas and DAX Formulas DAX formulas are similar to the Excel formulas and you can type them in the formula bar. However, there are some vital differences between the two. Excel Formula
DAX Formula
Excel formulas are typed in the formula bar in the Excel window.
DAX formulas are typed in the formula bar in the Power Pivot window.
In DAX formulas, you can reference only complete tables or columns of data, i.e. references can be only to tables and fields in the tables. In Excel formulas, you can reference individual cells or arrays for data.
Excel formulas support certain data types.
However, if at all you have to perform a calculation only on a part of the column data, you can do so with the DAX functions that filter and provide the required unique data values for calculation.
DAX provides more data types than Excel does. Hence, DAX formulas can use the additional data types also.
40
DAX
Excel does not support any implicit data conversions.
DAX performs implicit data type conversions during calculations.
Syntax for DAX Formulas DAX stands for Data Analysis Expressions. Henc e, the objective of DAX is to construct const ruct DAX formulas that enable you to perform data analysis activities.
A DAX formula always starts with an equals sign (=).
After the equals sign, you can provide an expression that evaluates to a scalar value, or an expression that can be converted to a scalar value. These include the following A scalar constant. o o
An expression that uses a scalar operator (+,-,*, /,>=, &&, etc.)
o
References to columns or tables.
o
Operators, constants, and values provided as part of an expression.
o
o
o
The result of a DAX function and its required arguments. Some DAX functions return a table instead of a scalar value, and must be wrapped in a DAX function that evaluates the table and returns a scalar value. Unless the t able is a single column, single row table, it is treated as a scalar value. Most DAX functions require one or more arguments, which can include tables, columns, expressions and values. However, some DAX functions, such as PI, do not require any arguments, but always require parentheses to indicate the null argument. For e.g., you must always type PI (), but not PI. You can also nest DAX functions within other DAX functions. Expressions. An expression can contain any or all of the following: operators, values, and references to columns.
Important Note: Note: DAX formulas can behave differently depending on whether they are used in a calculated column or in a calculated field. You must always be aware of the context and how the data that you use in the DAX formula is related to other data that might be used in the calculation. You will learn more about this in the chapter – DAX Context.
Examples
=[First Name] & [Last Name]
=CONCATENATE ([First Name], [Last Name])
Profit:=[Sales] – [Costs]
The first two are DAX formulas for calculated columns, whereas the third one is a DAX formula for a calculated field.
41
DAX
DAX Functions As in the case of Excel formulas, DAX formulas can also contain DAX functions. DAX has following types of functions –
Aggregation Functions
Date and Time Functions
Time Intelligence Functions
Filter Functions
Information Functions
Logical Functions
Math and Trig Functions
Other Functions
Parent and Child Functions
Statistical Functions
Text Functions
You will learn more about these in the chapter - DAX Functions. Functions.
DAX Table Naming Requirements Data Model comprises of tables between which relationships exist. It is the database for Power Pivot. Each table is stored on a separate tab in the Data Model and the table name is given as the tab name. Each table will have columns, representing the data fields. The column names will be displayed as the data fields in the PivotTable Fields list.
The tables in the Data Model must have unique names.
The table names are case insensitive. i.e., Results, RESULTS, results – all these represent the same table.
Each calculated column and calculated field that you add to a Data Model must be associated with a specific table. o
o
When you create a calculated column, you will do it within a table, thus associating it implicitly with that table. When you create a calculated field, you will create it - either within a table in the Power Pivot window – or from the Excel window wind ow specifying the name of the table where the calculated field definition should be stored.
In both the cases, the calculated cal culated field is termed as explicit calculated field and i s associated with a table.
42
DAX
DAX Naming Requirements Across Tables
You need to specify the table name in a DAX formula if the column is from a different table than the current table.
Table names must be enclosed in single quotation marks if they contain spaces, other special characters, or any non-English alphanumeric characters.
DAX Naming Requirements – Calculated Fields
The names of the calculated fields must always be in brackets.
The names of the calculated fields can contain spaces.
Each calculated field name must be unique within a Data Model. The table name is optional in front of a calculated field name as it is always associated with a specific table.
DAX Naming Requirements – Columns
Column names must be unique within a table. However, different tables can have columns with the same names.
The columns can be referenced with unqualified column names if there is n o name conflict. An unqualified column name is just the column name, enclosed in brackets. For e.g. [Medal]. When you are referencing a scalar value from the s ame row of the current table, you can use the unqualified column name.
However, if the same column name is used in more than one table, then the column has to be referenced with fully qualified column name. The fully qualified column name is the table name, followed by the column name in square brackets. For e.g. Results[Medal].
When you use a column as an input to a DAX function, it is a good practice to fully qualify the column name. The following DAX functions require column names to be fully qualified – o o o o o
VALUES. ALL and ALLEXCEPT. CALCULATE and CALCULATETABLE - in a filter argument. RELATEDTABLE. DAX time intelligence functions.
DAX Naming Requirements – Reserved Keywords If the name that you use for a table is the same as an Analysis Services reserved keyword, an error is raised and you must rename the table. However, you can use the keywords in the object names, if the object name is enclosed in brackets (for columns) or quotation marks (for tables).
43
DAX
Quotation marks can be represented by several different characters, depending on the application used. Hence, if you paste formulas from an external document or web page, make sure to check the ASCII code of the character that is used for opening and closing quotes, to ensure that they are the same. Otherwise , DAX may be unable to recognize the symbols as quotation marks, making the reference invalid.
DAX Fully Qualified Names – Special Cases If a table name contains spaces or reserved keywords or disallowed characters, you must enclose the table name within single quotation marks. For e.g. ‘East_Sales'[Amount]. You must enclose the table names in quotation marks, if the name contains any characters outside the ANSI alphanumeric character range, regardless of whether your locale supports the character set or not. For e.g., if a table name is written in Cyrillic characters, such as ‘Таблица’, the table name must be enclosed in quotation marks even though it does not contain spaces. You can use the formula AutoComplete feature, as i t will then be just a matter of selecti ng the fully qualified names of columns from the dropdown list, making your job easier and error proof.
Exclusion of Special Characters in Names The following characters and character types are not valid in the names of tables, columns, or calculated fields: Leading or trailing spaces, unless the spaces are enclosed by name delimiters, brackets, or single apostrophes. Control characters. The following characters are not valid in the names of Power Pivot objects: .,;':/\*|?&%$!+=()[]{}<>
Common DAX Formula Rules The DAX formula syntax depends on the typ e of operation it can perform and varies greatly if it contains DAX functions. Following rules apply to all the DAX formulas:
DAX formulas cannot modify or insert individual values in tables.
You cannot create calculated rows by using DAX. You can create only calculated columns and calculated fields.
When defining calculated columns, you can nest functions to any level (a maximum being 64 that is a bit too much).
DAX has several functions that return a table. Typically, you use the values returned by these DAX functions as an input to other DAX functions, which requi re a table as an input.
44
DAX
DAX Special Values The following table lists some DAX special values.
Special Value
Description
BLANK
A null, or a blank value, or an empty cell, or a missing value.
Infinity
Infinity value, as considered in mathematics (∞). DAX returns Infinity, if there is a division by zero.
NaN
Not a Number. DAX returns NaN for 0/0 and Infinity/Infinity.
If you divide an expression by Infinity, you will not get an error, as DAX returns 0.
DAX Operators The following table lists some DAX operators. Operator Type
Symbol
Use
Parenthesis Operators
()
Precedence order and grouping of arguments
+
Addition
-
Subtraction/ Sign
*
Multiplication
/
Division
^
Exponentiation
=
Equal to
>
Greater than
<
Less than
Arithmetic Operators
Comparison Operators
Text Concatenation Operator
>=
Greater than or equal to
<=
Less than or equal to
<>
Not equal to
&
Concatenation
&&
AND
||
OR
Logical Operators
45
DAX
DAX operators and Excel operators behav e in a similar way with some exceptions. You will learn more about DAX operators in the chapter - DAX Operators.
New Data Types in DAX Table is a new data type in Data Model.
You can use a table containing multiple columns and multiple rows of data as an argument to a DAX function.
Some DAX functions return tables, which are stor ed in memory and can be used as arguments to other DAX functions.
Datetime is another new data type in Data Model that is used for date and time values. DAX functions that require date and/or time as argu ments, require the datetime data type.
Summary of DAX Data Types Following are the data types supported by DAX: Data Type
Description
A 64 bit (eight-bytes) integer value
Numbers that have no decimal places. Integers can be positive or negative numbers, but must be whole numbers between 9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1).
Real numbers are numbers that can have decimal places. Real numbers cover the following values:
A 64 bit (eight-bytes) real number
Negative values from -1.79E +308 through 2.23E -308
Zero
Positive values from 2.23E -308 through 1.79E + 308
However, the number of significant digits is limited to 15 decimal digits.
Boolean
True
False
46
DAX
String
A Unicode character data string. Can be strings, numbers or dates represented in a text format. Maximum string length is 268,435,456 Unicode characters (256 mega characters) or 536,870,912 bytes.
datetime
Dates and times in an accepted date-time representation. Valid dates are all dates after January 1, 1900.
Currency
Currency data type allows values between 922,337,203,685,477.5808 to 922,337,203,685,477.5807 with four decimal digits of fixed precision.
Blank
A blank is a data type in DAX that represents and replaces SQL nulls. You can create a blank by using the BLANK function, and test for blanks by using the logical function, ISBLANK.
Table
Represents a table in the data model.
Note: Data Model does not support the use of the variant data type used in Excel. Hence, when you load or import data, ensure that the data in each column of a table is of consistent data type. DAX functions work with the following data types –
Scalar values, including strings.
Numbers, both integers and real numbers.
Dates and times.
DAX functions return error in case of data type incompatibility. Ensure that the data type of each column in a table is as required. If not, you can explicitly set the data type from the Ribbon in the Power Pivot window. You can learn about the data types required for each DAX function in the tutorial - DAX Functions in this tutorials library.
47
DAX
Implicit Data Type Conversion in DAX Formulas You do not need to cast, convert, or otherwise specify the data type of a column or a value that you use in a DAX formula. When you use data in a DAX formula, DAX automatically identifies the data types in referenced columns and of the values that you type in and performs implicit conversions where necessary to complete the specified operation. However, there are some limitations on the values that can be successfully converted. If a value or a column has a data type that is incompatible with the current operation, DAX returns an error. Example of implicit data conversion in DAX: Suppose you have a DAX formula wherein you are adding a number to a Date. DAX interprets it in the context of function that is used. Both the arguments are converted to a common data type and the result is returned in the intended data type. The type of conversion that is performed is determined by the operator, which casts the values it requires before performing the requested operation. In the following sections, you can find the tables of implicit data conversions for the following operators –
Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
Comparison Operators
Each of the tables list the operators and indicate the conversion that is performed on each data type in the column when it is paired with the data type in the intersecting row.
Implicit Data Conversion Table for Addition (+) Operator (+)
INTEGER
CURRENCY
REAL
DATETIME
INTEGER
INTEGER
CURRENCY
REAL
DATETIME
CURRENCY
CURRENCY
CURRENCY
REAL
DATETIME
REAL
REAL
REAL
REAL
DATETIME
DATETIME
DATETIME
DATETIME
DATETIME
DATETIME
For example, if A is of Currency data type and B is a Real data type, then while calculating A+B, DAX converts A into Real and adds it to B. The result will be a Real data type.
48
DAX
Implicit Data Conversion Table for Subtraction (-) The row header is the minuend (l eft side) and the column header is the subtrahend (right side). Operator (-)
INTEGER
CURRENCY
REAL
DATETIME
INTEGER
INTEGER
CURRENCY
REAL
REAL
CURRENCY
CURRENCY
CURRENCY
REAL
REAL
REAL
REAL
REAL
REAL
REAL
DATETIME
DATETIME
DATETIME
DATETIME
DATETIME
For example, if A is of Integer data type and B is of Real data type, then while calculating A-B, DAX converts A into Real and subtracts B from A. The r esult will be a Real data type. Note: Data Model also supports the unary operator, - (negative), but this operator does not change the data type of the operand.
Implicit Data Conversion Table for Multiplication (*) Operator (*)
INTEGER
CURRENCY
REAL
DATETIME
INTEGER
INTEGER
CURRENCY
REAL
INTEGER
CURRENCY
CURRENCY
REAL
CURRENCY
CURRENCY
REAL
REAL
CURRENCY
REAL
REAL
For example, if A is of Real data type and B is of Currency data type, then to calculate A*B, DAX converts A to Currency data type and multiplies A with B. The result will be a Currency data type.
Implicit Data Conversion Table for Division (/) Operator (/)
INTEGER
CURRENCY
REAL
DATETIME
INTEGER
REAL
CURRENCY
REAL
REAL
CURRENCY
REAL
CURRENCY
REAL
REAL
REAL
REAL
REAL
REAL
REAL
DATETIME
REAL
REAL
REAL
REAL
For example, if A is Currency data type and B is of Real data type, then while calculating A/B, DAX will convert A to Real number and performs the division. The result will be a Real data type.
49
DAX
Implicit Data Conversion Table for Comparison Operators In comparison expressions, DAX follows the rules mentioned below –
Boolean values are considered greater than string values.
String values are considered greater than numeric or datetime values.
Numbers and datetime values are considered to have the same rank.
No implicit data conversions are performed for Boolean or string values.
BLANK or a blank value is converted to 0/""/false depending on the data type of the other compared value.
For numeric or datetime types, data conversions are performed implicitly as shown in the following table:
Comparison
INTEGER
CURRENCY
REAL
DATETIME
INTEGER
INTEGER
CURRENCY
REAL
REAL
CURRENCY
CURRENCY
CURRENCY
REAL
REAL
REAL
REAL
REAL
REAL
REAL
DATETIME
REAL
REAL
REAL
DATETIME
Operator
How DAX Handles Blanks, Empty Strings, and Zero Values? In DAX, a null, a blank value, an empty cell, or a missing value are all represented by the same special value, a BLANK.
You can generate a BLANK with the DAX BLANK function.
You can test for a BLANK by using the DAX ISBLANK function.
How DAX handles blanks in DAX formulas or DAX functions depends on the individual operation such as addition or concatenation or the specific DAX function.
50
DAX
Examples DAX Formula
Result
BLANK + BLANK
BLANK
BLANK + 5
5
BLANK * 5
BLANK
5 / BLANK
Infinity
0 / BLANK
NaN
BLANK / BLANK
BLANK
FALSE OR BLANK
FALSE
FALSE AND BLANK
FALSE
TRUE OR BLANK
TRUE
TRUE AND BLANK
FALSE
BLANK OR BLANK
BLANK
BLANK AND BLANK
BLANK
51
7. DAX ─ Operators
DAX
DAX is a formula language comprising of functions, operators, and values that can b e used in a formula or expression, to calculate and return one or more values. You can use DAX operators to compare values, perform arithmetic calculations, and concatenate strings. In this chapter, you will learn about DAX operators and how to use them.
Types of DAX Operators DAX supports the following types of operators –
DAX Arithmetic Operators
DAX Comparison Operators
DAX Text Concatenation Operator
DAX Logical Operators
DAX Arithmetic Operators You can use DAX arithmetic operators to perform basic mathematical operations such as addition, subtraction, multiplication, division, and exponentiation. These DAX operators cast numeric data type to the values with which they are used, if they are not already numeric values. They produce numeric results after the calculations. Example
Arithmetic Operator
Mathematical Operation
+
Addition
5+4
9
Subtraction
5-4
1
Sign
-4
-4
*
Multiplication
5*3
15
/
Division
16/4
4
^
Exponentiation
4^2
16
-
Result
The above DAX arithmetic operators are known as binary operators, meaning they combine two values on either side, as you can observe in the examples above.
52
DAX
The DAX arithmetic operators + and can also be used as unary operators, meaning that the DAX operator can be used only with one value. For example, +5, -4. These unary operators can be used with any data type. However, there is a subtle difference between the two – –
The + operator when used with a value or a result, it does not convert the data type of the value. It is just ignored. For example, + Seasons is same as Seasons.
On the other hand, the operator when used with a value or a result, converts the data type of the value to a negative numeric value. For example, – Seasons = -4 if the value of Seasons is 4. –
DAX Comparison Operators You can use the DAX comparison operators t o compare two values producing a result that is a logical value, i.e. either TRUE or FALSE. DAX supports the following comparison operators.
Comparison Operator
Comparison Operation
Example
Result
=
Equal to
5=4
FALSE
>
Greater than
5>4
TRUE
<
Less than
5<4
FALSE
>=
Greater than or equal to
5>=3
TRUE
<=
Less than or equal to
3<=5
TRUE
<>
Not equal to
5<>4
TRUE
DAX Text Concatenation Operator You can use the DAX text concatenation operator to concatenate or join two or more text strings to produce a single continuous and combined text string. Text Concatenation Operator
&
Operation
Concatenation / Join
Example
“Hyderabad”&”, ”&”India”
Result
Hyderabad, India
53
DAX
DAX Logical Operators You can use DAX logical operators to combine expressions that evaluate to a logical or Boolean value (TRUE or FALSE), to produce a single result that is logical (TRUE or FALSE). Logical Operator
!
Logical Operation
Evaluation
NOT
It is a unary operator. This means it takes only one operand. The result is TRUE if the operand evaluates to FALSE.
FALSE if the operand evaluates to TRUE.
&&
AND
TRUE only if both the expressions are TRUE. Otherwise, FALSE.
||
OR
FALSE only if both the expressions are FALSE. Otherwise, TRUE.
Examples
! (1=1) results in FALSE, because 1=1 is TRUE.
! (1 <> 1) results in TRUE, because 1 <> 1 is FALSE.
(1=1) && (5>4) results in TRUE, because both are TRUE.
(5>4) && (5=4) results in FALSE, because 5=4 is FALSE.
(1=1) || (5=4) results in TRUE, because 1=1 is TRUE.
(2=4) || (5=4) results in FALSE, because both are FALSE.
DAX Operator Precedence Order You can have a DAX formula with many DAX operators combining several values or expressions. In such a case, the final result will depend on the order in which the operations are performed. DAX provides you with the default operator precedence order and also ways of overriding the default precedence order.
54
DAX
DAX default operator precedence is listed in the following table. Precedence Order
Operator(s)
Operation
1
^
Exponentiation
2
–
Sign
3
* and /
Multiplication and Division
4
!
NOT
5
+ and –
Addition and Subtraction
6
&
Concatenation
7
=, <, >, <=, >= and <>
Equal to, Less than, Greater than, Less than or equal to, Greater than or equal to and Not equal to
DAX Expression Syntax You need to first understand the DAX expre ssion syntax and how the expression evaluation is done with the operands and operators.
All expressions always begin with an equal sign (=). The equal sign indicates that the succeeding characters constitute an expression.
To the right of the equal sign, you will have the operands connected by the DAX operators. For example, = 5+4>5. = 5*6-3.
Expressions are always read from left to right, and the calculations are done in that sequence, based on the DAX operator precedence given in the previous section.
If the DAX operators have equal precedence value, they are evaluated from the left to right. For example, =5*6/10. Both * and / have same the precedent order. Hence, the expression is evaluated as 30/10 = 3.
If the DAX operators in the expression have different precedence values, then they are evaluated in the precedence order from the left to right. o
=5+4>7. Default precedence is + first and > next. Hence, the expression is calculated from the left to right. - 5+4 is calculated first resulting in 9 and then 9 > 5 is evaluated that results in TRUE. 55
DAX
o
o
= 5*6-3. Default precedence is * fi rst and - next. Hence, the expression is calculated from the left to right. - 5*6 i s calculated first resulting in 30 and then 30 - 3 is calculated that results in 27. = 2*5-6*3. Default precedence is * first, * next and then -. Hence, the expression evaluates as 10 – 18 and then as -8. Note, that it is not 10-6 resulting in 4 and then 4*3 that is 12.
Using Parentheses to Control DAX Calculation Order You can change the DAX default operator precedence order by using parentheses, grouping the operands and the operators to control the calculation sequence. For example, = 5*6-3 evaluates to 27 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 5*(6 -3), then 6-3 is calculated first resulting in 3 and then 5*3 is calculated which results in 15. =2*5-6*3 evaluates to -8 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 2*(5-6)*3, then 5-6 is calculated first resulting in -1 and then 2*(-1)*3 is calculated which results in -6. As you can see, with the same operands and operators, different results are possible by the way you group them. Hence, when you use the DAX operators in the DAX formulas, you should pay attention to how the computation sequence is to be.
Differences Between Excel and DAX Though DAX has similarities with Excel formulas, there are certain significant differences between the two.
DAX is more powerful than Excel because of its underlying memory resident computation engine.
DAX supports more data types than Excel.
DAX provides additional advanced features of a relational database, Data Model, including richer support for date and time types.
In some cases, the results of calculations or the behavior of functions in DAX may not be the same as in Excel. This is due to the differences in the following
Data type casting
Data types
Difference in Data Type Casting In DAX, when you have an expression =value1 operator value2, the two operands value1 and value2 should be of the same data type. If the data types are different, DAX will convert them first to a common data type implicitly. Refer to the chapter – DAX Syntax for details.
56
DAX
For example, you have to compare two operands of different data types, say a number resulting from a formula, such as =[Amount] * 0.08 and an integer. The first number c an be a decimal number with many decimal places, whereas the second number is an integer. Then DAX handles it as follows –
First, DAX will convert both the operands to real numbers using the largest numeric format that can store both kinds of numbers.
Next, DAX will compare the two real numbers.
In contrast, Excel tries to compare values of different data types without first coercing them to a common data type. For this reason, you might find different results in DAX and in Excel for the same comparison expression.
Difference in Data Types The operator precedence order in DAX and Excel is the same. However, the operator percent (%) and data ranges that Excel supports are not supported by DAX. Moreover, DAX supports table as a data type, which is not the case in Excel. Further, in Excel formulas, you can refer to a single cell, or an array or a range of cells. In DAX formulas, you cannot refer to any of these. The DAX formula references t o data should be by tables, columns, calculated fields, and calculated columns. If you copy formulas from Excel and paste them in DAX, ensure the correctness of the DAX formula as DAX syntax is different from Excel formula syntax. Al so, even if a function has the same name in DAX and Excel, its parameters might be different and the result of the function can also be different. You will learn more about all these in the subsequent chapters.
57
8. DAX – Standard Parameters
DAX
DAX has standard parameter names to facilitate the usage and understanding of the DAX functions. Further, you can use c ertain prefixes to the parameter names. If the prefix is clear enough, you can use the prefix itself as the parameter name.
Standard Parameter Names Following are the DAX standard parameter names – Parameter Name
Description
expression
Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
value
Any DAX expression that returns a single scalar value where the expression is to be evaluated exactly once before all other operations.
table
Any DAX expression that returns a table of data.
tableName
The name of an existing table using standard DAX syntax. It cannot be an expression.
columnName
The name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression.
name
A string constant that will be used to provide the name of a new object.
order
An enumeration used to determine the sort order.
ties
An enumeration used to determine the handling of tie values.
type
An enumeration used to determine the data type for PathItem and PathItemReverse.
58
DAX
Prefixing Parameter Names You can qualify a parameter name with a prefix –
The prefix should be descriptive of how the argument is used.
The prefix should be in such a way that ambiguous reading of the parameter is avoided.
For example,
Result_ColumnName - Refers to an existing column used to get the result values in the DAX LOOKUPVALUE () function.
Search_ColumnName - Refers to an existing column used to search for a value in the DAX LOOKUPVALUE () function.
Using Only the Prefix as a Parameter You can omit the parameter name and use only the prefix, if the prefix i s clear enough to describe the parameter. Omitting the parameter name and using only the prefix can sometimes help in avoiding the clutter in reading. For example, Consider DATE (Year_value, Month_value, Day_value). You can omit the parameter name – value, that is repeated thrice and write it as DATE (Year, Month, Day). As you can observe, by using only the pre fixes, the function is more readable. However, sometimes the parameter name and the prefix have to be present for clarity. For example, Consider Year_columnName. The parameter name is ColumnName and the prefix is Year. Both are required to make the user understand that the parameter requires a reference to the existing column of years.
59
9. DAX ─ Functions
DAX
Most of the DAX functions have the same names and functionality as that of Excel functions. However, DAX functions have been modifi ed to use DAX data types and to work with tables and columns. DAX has some additional functions that you will not find in Excel. These DAX functions are provided for specific purposes such as lookups based on relationships associated with the relational database aspects of the Data Model, the ability to iterate over a table to perform recursive calculations, to perform dynamic aggregation, and for calculations utilizing time intelligence. In this chapter, you will learn about the functions supported in the DAX language. For more information on the usage of these DAX functions, refer to the tutorial – DAX Functions in this tutorials library.
What is a DAX Function? A DAX function is an in-built function provided in the DAX language to enable you to perform various actions on the data in the tables in your Data Model. As discussed earlier, DAX is used for data analysis and business intelligence purposes that require support to extract, assimilate, and derive insights from the data. The DAX functions that are based on the Data Model provide you with these utilities that make your job simpler, once you get a grasp on the DAX language and the usage of the DAX functions.
Excel Functions vs. DAX Functions There are certain similarities between Excel functions that you are aware of and the DAX functions. However, there are certain differences too. You need to get a clarity on these, so that you can avoid making mistakes in the usage of DAX functions and in writing DAX formulas that include DAX functions.
Similarities between Excel Functions and DAX Functions
Many DAX functions have the same name and the same general behavior as Excel functions.
DAX has lookup functions that are similar to the array and vector lookup functions in Excel.
60
DAX
Differences between Excel Functions and DAX Functions
DAX functions have been modified to take different types of inputs and some of the DAX functions might return a different data type. Hence, you need to understand the usage of these functions separately though they have the same name. In this tutorial, you will find every DAX function prefixed with DAX so as to avoid confusion with the Excel functions.
You cannot use DAX functions in an Excel formula or use Excel formulas/functions in DAX, without the required modifications.
Excel functions take a cell reference or a range of cells as reference. DAX functions never take a cell reference or a range of cells as reference, but instead take a column or table as reference.
Excel date and time functions return an integer that represents a date as a serial number. DAX date and time functions return a datetime data type that is in DAX but not in Excel.
Excel has no functions that return a table, but some functions can work with arrays. Many of the DAX functions can easily reference complete tables and columns to perform calculations and return a table or a column of values. This ability of DAX adds power to the Power Pivot, Power View and Power BI, where DAX is used.
DAX lookup functions require that a relationship is established between the tables.
Excel supports variant data type in a column of data, i.e. you can have data of different data types in a column. Whereas, DAX expects the data in a column of a table to be always of the same data type. If the data is not of the same data type, DAX changes the entire column to the data type that best accommodates all the values in the column. However, if the data is imported and this issue arises, DAX can flag an error.
To learn about DAX data types and data type casting, refer to the chapter – DAX Syntax Reference.
Types of DAX Functions DAX supports the following types of functions.
DAX Table Valued Functions DAX Filter Functions o o
DAX Aggregation Functions
o
DAX Time Intelligence Functions
DAX Date and Time Functions
DAX Information Functions
DAX Logical Functions
DAX Math and Trig Functions
DAX Other Functions
61
DAX
DAX Parent and Child Functions
DAX Statistical Functions
DAX Text Functions
In this section, you will learn about DAX functions at the functions category level. For details on the DAX Function Syntax and what the DAX function returns and does - refer to the DAX Functions tutorial in this tutorials library. DAX time intelligence functions and DAX filter functions are powerful and require a spe cial mention. Refer to the chapters - Understanding DAX Time Intelligence and DAX Filter Functions for details.
DAX Table Valued Functions Many DAX functions take tables as input or output tables or do both. These DAX functions are called DAX table valued functions. Because a table can have a single column, DAX table valued functions also take single columns as inputs. You have the following types of DAX table valued functions –
DAX Aggregation functions
DAX Filter functions
DAX Time intelligence functions
Understanding DAX table valued functions helps you in writing DAX formulas effectively.
DAX Aggregation Functions DAX Aggregation functions aggregate any expression over the rows of a table and are useful in calculations. Following are some DAX Aggregation functions:
ADDCOLUMNS (
, , , [, ] …)
AVERAGE ()
AVERAGEA ()
AVERAGEX (
, )
COUNT ()
COUNTA ()
COUNTAX (
, )
COUNTBLANK ()
COUNTROWS (
)
COUNTX (
, )
CROSSJOIN (, , [] …)
DISTINCTCOUNT ()
GENERATE (, )
GENERATEALL (, )
MAX () 62
DAX
MAXA ()
MAXX (
, )
MIN ()
MINA ()
MINX (
, )
PRODUCT () PRODUCTX (
, )
ROW (, , [, ] …)
SELECTCOLUMNS (
, , ,
[, ] …)
SUM ()
SUMMARIZE (
, , [] …, [, ] …)
SUMX (
, )
TOPN (,
, [, []] …)
,
[],
DAX Filter Functions DAX Filter functions return a column, a table, or values related to the current row. You can use DAX Filter functions to return specific data types, look up values in related tables, and filter by related values. DAX Lookup functions work by using tables and relationships between them. DAX Filter functions enable you to manipulate the data context to create dynamic calculations. Following are some DAX Filter functions:
ADDMISSINGITEMS(, [] , [] … [filterTable] …)
ALL( {
| , [ ], [] …} )
ALLEXCEPT(
, , [] …)
ALLNOBLANKROW(
|)
ALLSELECTED([ | ])
CALCULATE (, , …)
CALCULATETABLE (, , …)
CROSSFILTER (, , )
DISTINCT ()
EARLIER(, )
EARLIEST()
FILTER(
,)
FILTERS()
…,
,
63
DAX
HASONEFILTER()
HASONEVALUE()
ISCROSSFILTERED ()
ISFILTERED ()
KEEPFILTERS ()
RELATED()
RELATEDTABLE()
SUBSTITUTEWITHINDEX (
, , , , [], [, []] …])
USERELATIONSHIP(,)
VALUES()
DAX Time Intelligence Functions DAX Time Intelligence functions return a table of dates or use a table of dates to calculate an aggregation. These DAX functions help you create calculations that support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters and years. Following are some DAX Time Intelligence functions:
CLOSINGBALANCEMONTH (,[,])
CLOSINGBALANCEQUARTER (,, [])
CLOSINGBALANCEYEAR (,, [], [])
DATEADD (,, )
DATESBETWEEN (,,)
DATESINPERIOD (,, ,)
DATESMTD ()
DATESQTD ()
DATESYTD (, [])
ENDOFMONTH ()
ENDOFQUARTER ()
ENDOFYEAR ( , [])
FIRSTDATE ()
FIRSTNONBLANK (,)
LASTDATE ()
LASTNONBLANK (,)
NEXTDAY ()
NEXTMONTH ()
NEXTQUARTER ()
NEXTYEAR (, []) 64
DAX
OPENINGBALANCEMONTH (,, [])
OPENINGBALANCEQUARTER (,, [])
OPENINGBALANCEYEAR (,, [], [])
PARALLELPERIOD (,, )
PREVIOUSDAY()
PREVIOUSMONTH()
PREVIOUSQUARTER()
PREVIOUSYEAR (, [])
SAMEPERIODLASTYEAR()
STARTOFMONTH()
STARTOFQUARTER()
STARTOFYEAR()
TOTALMTD (,, [])
TOTALQTD(,, [])
TOTALYTD(,, [], [])
DAX Date and Time Functions DAX Date and Time functions are similar to the Excel date and time functions. However, DAX Date and Time functions are based on the datetime data type of DAX. Following are DAX Date and Time functions:
