32,36 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Implement business intelligence (BI), data modeling, and data analytics within Microsoft products such as Power BI, SQL Server, and Excel
Key Features
- Understand the ins and outs of DAX expressions and querying functions with the help of easy-to-follow examples
- Manipulate data of varying complexity and optimize BI workflows to extract key insights
- Create, monitor, and improve the performance of models by writing clean and robust DAX queries
Book Description
Data Analysis Expressions (DAX) is known for its ability to increase efficiency by extracting new information from data that is already present in your model. With this book, you'll learn to use DAX's functionality and flexibility in the BI and data analytics domains.
You'll start by learning the basics of DAX, along with understanding the importance of good data models, and how to write efficient DAX formulas by using variables and formatting styles. You'll then explore how DAX queries work with the help of examples. The book will guide you through optimizing the BI workflow by writing powerful DAX queries. Next, you'll learn to manipulate and load data of varying complexity within Microsoft products such as Power BI, SQL Server, and Excel Power Pivot. You'll then discover how to build and extend your data models to gain additional insights, before covering progressive DAX syntax and functions to understand complex relationships in DAX. Later, you'll focus on important DAX functions, specifically those related to tables, date and time, filtering, and statistics. Finally, you'll delve into advanced topics such as how the formula and storage engines work to optimize queries.
By the end of this book, you'll have gained hands-on experience in employing DAX to enhance your data models by extracting new information and gaining deeper insights.
What you will learn
- Understand DAX, from the basics through to advanced topics, and learn to build effective data models
- Write and use DAX functions and expressions with the help of hands-on examples
- Discover how to handle errors in your DAX code, and avoid unwanted results
- Load data into a data model using Power BI, Excel Power Pivot, and SSAS Tabular
- Cover DAX functions such as date, time, and time intelligence using code examples
- Gain insights into data by using DAX to create new information
- Understand the DAX VertiPaq engine and how it can help you optimize data models
Who this book is for
This book is for data analysts, business analysts, BI developers, or SQL users who want to make the best use of DAX in the BI and data analytics domain with the help of examples. Some understanding of BI concepts is mandatory to fully understand the concepts covered in the book.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 382
Veröffentlichungsjahr: 2020
Ähnliche
Hands-On Business Intelligence with DAX
Copyright © 2020 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor:Sunith ShettyAcquisition Editor:Reshma RamanContent Development Editor:Nazia ShaikhSenior Editor: Ayaan HodaTechnical Editor: Dinesh ChaudharyCopy Editor: Safis EditingProject Coordinator:Aishwarya MohanProofreader: Safis EditingIndexer:Priyanka DhadkeProduction Designer:Nilesh Mohite
First published: January 2020
Production reference: 1300120
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-83882-430-3
www.packt.com
Subscribe to our online digital library for full access to over 7,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Fully searchable for easy access to vital information
Copy and paste, print, and bookmark content
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.packt.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.packt.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the author
Ian Horne is head of data services with a global organization. He is a business intelligence (BI) professional with over 30 years of experience and specializes in the design, development, and maintenance of corporate databases, data warehouses, associated ETL processes, and end user reporting. He has extensive knowledge of the Microsoft BI stack, including SQL Server, SSRS, SSAS, Power BI, and, of course, DAX. Ian holds a diploma in computing and a Bachelor of Science degree from the Open University. In his spare time, he creates training videos on Power BI, DAX, and other data-related matters, which he shares through his YouTube channel, Data World TV.
About the reviewers
Felipe Vilela has a master's degree in BI and data warehousing from IESB University and has several certifications from MicroStrategy and others, such as Tableau and Alteryx. He has worked for more than 10 years on BI projects with big US companies and has also been a technical reviewer for two of Packt's MicroStrategy books.
Juan Tomás Oliva Ramos is an environmental engineer from the University of Guanajuato, Mexico, with a master's degree in administrative engineering and quality. He now works in the Tecnologico Nacional de México campus Purísima del Rincón, Guanajuato. He has more than 5 years experience in the management and development of patents, technological innovation projects, and technological solutions through the statistical control of processes. He has been a teacher of statistics, entrepreneurship, and technological development since 2011. He has developed prototypes via programming and automation technologies for the improvement of operations, all of which have been registered for patents.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Title Page
Copyright and Credits
Hands-On Business Intelligence with DAX
Dedication
About Packt
Why subscribe?
Contributors
About the author
About the reviewers
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Get in touch
Reviews
Section 1: Introduction to DAX for the BI Pro
What is DAX?
Introducing DAX
Working with data types and operators
Data types
Operators
Working with calculated columns and measures
Calculated columns
Measures
Calculated columns versus measures
Evaluation contexts – part 1
Row context
Filter context
Using the CALCULATE function
Summary
Using DAX Variables and Formatting
Getting started with DAX variables
Variable nesting
Using variables with measures, calculated columns, and tables
Formatting your DAX code
Debugging errors in your DAX code
Summary
Building Data Models
Introduction to data modeling
Data modeling concepts in DAX
Fact tables and dimension tables
Star schema and snowflake schema
Relationships
Cardinality
Cross filter direction
Hierarchies
Getting data into your data model
Building your first data model
Extending your data model
Adding a calculated column
Adding a calculated table
Adding a measure
It's a date
Summary
Working with DAX in Power BI, Excel, and SSAS
Working with DAX in Power BI Desktop
The DAX formula editor
Working with DAX in Excel Power Pivot
Installing and enabling the Power Pivot add-in
Adding data to an Excel data model
Extending an Excel data model
Working with DAX in SSAS Tabular
Importing the Excel Power Pivot data model into the SSDT project
Deploying your data model to an instance of SSAS Tabular
Working with the tabular database in SSMS
Querying SSAS Tabular data using DAX
Summary
Getting It into Context
Introducing evaluation contexts – part 2
Deep diving into row context 
Iterator functions
Deep diving into filter context 
Expanded tables
Changing context using DAX functions
Context transition
Changing the filter context
Using the ALL function
Using filters with CALCULATE
Summary
Section 2: Understanding DAX Functions and Syntax
Progressive DAX Syntax and Functions
Breaking down DAX syntax
Naming requirements
Dealing with relationships
Dealing with multiple relationships
Virtual relationships
Looking at DAX functions
Function types
Function groups
Introduction to aggregation functions
Aggregation function reference
The MIN, MINA, and MINX functions
Functions for parent-child hierarchies
Parent and child function reference
The PATH, PATHCONTAINS, and PATHLENGTH functions
The PATHITEM and PATHITEMREVERSE functions
Summary
Table Functions
Introducing table functions
Creating a DAX calculated table
Using a table expression as a table function parameter
Querying your data model using table functions
Looking at table manipulation functions
Table manipulation functions reference
The CROSSJOIN function
The DATATABLE function
The EXCEPT, INTERSECT, and UNION functions
The GENERATESERIES function
Working with table functions
The COUNTROWS function
The PRODUCTX function
The CONTAINS function
The CONCATENATEX function
Summary
Date, Time, and Time Intelligence Functions
Introduction to date and time functions
Date and time function reference
Working with date and time functions
Building a date table
The CALENDAR and CALENDARAUTO functions
The DATEDIFF function
The EDATE function
The EOMONTH function
The YEARFRAC function
Looking at time intelligence functions
Time intelligence function reference
Making your data more intelligent over time
DAX functions that return a single date
Comparing values over different periods of time
The opening and closing balance functions
Summary
Filter Functions
Introduction to filter functions
Filter function reference
Filtering your data with filter functions
The ALL and ALLEXCEPT functions
The ALLSELECTED function
The FILTER function
The KEEPFILTERS function
The LOOKUPVALUE function
The SELECTEDVALUE function
Summary
Statistical Functions
Introducing statistical functions
Statistical function reference
Calculating averages
The AVERAGE function
Calculating rolling averages with the AVERAGEX function
Working with percentiles
The PERCENTILE.EXC and PERCENTILE.INC functions
The PERCENTILEX.EXC and PERCENTILEX.INC functions
The MEDIAN and MEDIANX functions
Ranking your data
The RANK.EQ function
The RANKX function
Calculating standard deviation and variance
Summary
Working with DAX Patterns
Introducing Power BI Quick Measures
Creating your first quick measure
Calculating cumulative totals
Binning data using segmentation
Comparing equivalent periods
Comparing previous periods
Comparing the period-on-period percentages
Calculating period-to-date totals
Working with mathematical patterns
Summary
Section 3: Taking DAX to the Next Level
Optimizing Your Data Model
Introducing the VertiPaq engine
Value encoding
Dictionary encoding
RLE
Understanding your data model
Data profiling with Power BI Desktop
Data profiling in SSAS Tabular and Excel Power Pivot
Simplifying your data model
Understanding your source data
Keeping your data model simple
Using a star schema
Merging and appending tables
Importing required rows and columns only
Using the correct data type
Using measures instead of calculated columns
Creating summary tables
Summary
Optimizing Your DAX Queries
Introduction to the DAX calculation engines
The formula engine
The storage engine
Monitoring performance with DAX Studio
Viewing performance with DAX Traces
View VertiPaq metrics
Using SQL Server Profiler
Using Power BI Performance Analyzer
Summary
Other Books You May Enjoy
Leave a review - let other readers know what you think
Preface
DAX provides an extra edge by extracting key information from the data that is already present in your model. With this book, you will leverage DAX's functionality and flexibility in the business intelligence (BI) and data analytics domain.
You'll start with the basics of DAX, along with the importance of good data models, and how to write efficient DAX formulas by using variables and good formatting. You will learn how DAX queries work using an example-based approach. You will learn how to optimize your BI workflow by writing efficient and powerful DAX queries with easy-to-follow explanations and examples. You will learn how to manipulate and load datasets of different complexities within various Microsoft products, such as Power BI, SQL Server, and Excel Power Pivot. You will learn how to build and extend your data models to gain additional insights. Later, you will delve into progressive DAX syntax and functions to understand complex relationships in DAX. You will cover important DAX functions, specifically those related to tables, date and time, filtering, and statistics. You will then move on to more advanced topics, such as how the formula and storage engines work to be able to optimize your queries.
By the end of this book, you will be able to employ DAX to enhance your data model by extracting new information and gaining deeper insights.
Who this book is for
This book is for data analysts, business analysts, BI developers, or any SQL users who want to get the best out of DAX in the BI and data analytics domain using an example-rich guide. You will learn how to use DAX queries in Power BI, Excel, and SQL Server to carry out efficient analysis. Some understanding of BI concepts is mandatory.
What this book covers
Chapter 1, What is DAX?, gives you an overview of the DAX language, what it is, and how, as a BI pro, you can use it to create new information from existing data. It will introduce the different calculation types, calculated columns and measures, and the CALCULATE function, probably the most important DAX function.
Chapter 2, Using DAX Variables and Formatting, teaches you about using variables in DAX formulas and how these can make your DAX code easier to read and potentially more efficient. You will also look at recommended formatting styles for DAX code. Finally, you'll look at error handling and how using variables can make this easier.
Chapter 3, Building Data Models, talks about the importance of building a well-defined data model, both from the point of view of a BI professional and in terms of making DAX easier to use.
Chapter 4, Working with DAX in Excel, Power BI, and SSAS, looks at the three different platforms that support DAX – Excel, Power BI, and SSAS Tabular. You will look at loading data in more depth and the different ways DAX is used in each.
Chapter 5, Getting it into Context, moves beyond the basics and builds upon what you learned about evaluation contexts in the first chapter. You will learn about the difference between the row context and the filter context and how these affect DAX functions. You will also take a more in-depth look at the CALCULATE function.
Chapter 6, Progressive DAX Syntax and Functions, explores the structure of DAX syntax, and you'll look at the groups of functions currently available in DAX. You'll take a more in-depth look at relationships, aggregation functions, and parent-child functions, all with hands-on examples.
Chapter 7, Table Functions, concerns the DAX table functions and includes details of the syntax of each function, as well as an explanation of how each works. Finally, you'll get hands-on with some practical examples of the functions being used.
Chapter 8, Date, Time, and Time Intelligence Functions, is where you will learn about the DAX date, time, and time intelligence functions, with details of the syntax of each function, including an explanation of how each works. Finally, you'll get hands-on with some practical examples of the functions being used.
Chapter 9, Filter Functions, moves on to the DAX filter functions, with details of the syntax of each function, including an explanation of how each works. Finally, you'll get hands-on with some practical examples of the functions being used.
Chapter 10, Statistical Functions, covers the DAX statistical functions, with details of the syntax of each function, including an explanation of how each works. Finally, you'll get hands-on with some practical examples of the functions being used.
Chapter 11, Working with DAX Patterns, is the final chapter of part 2, and you will look at some examples of DAX being used in the form of DAX patterns. Each pattern will have a walkthrough involving a practical example that breaks down the code and gives a detailed explanation of how it works.
Chapter 12, Optimizing Your Data Model, delves into the VertiPaq engine and how it can be used to help you optimize your data model. You'll also look at some ways in which you can optimize your data model along with how and why this may improve performance.
Chapter 13, Optimizing Your DAX Queries, shows you some techniques that will help to make your DAX calculations more efficient, including a look at some tools to help you analyze query performance. You'll also look at the two DAX calculation engines: the storage engine and the formula engine.
To get the most out of this book
You should be familiar with general BI concepts. A basic understanding of using Power BI, Excel, and SQL Server to carry out efficient analysis is mandatory.
Download the example code files
You can download the example code files for this book from your account at www.packt.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packt.com
.
Select the
Support
tab.
Click on
Code Downloads
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/Hands-On-Business-Intelligence-with-DAX. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: http://www.packtpub.com/sites/default/files/downloads/9781838824303_ColorImages.pdf.
Get in touch
Feedback from our readers is always welcome.
General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packt.com.
Section 1: Introduction to DAX for the BI Pro
This section covers the basics of DAX, along with the importance of good data models and how to write efficient DAX formulas by using variables and good formatting. You will understand how DAX queries work using an example-based approach. You will also learn how to optimize the BI workflow by writing efficient and powerful DAX queries with easy-to-follow explanations and examples:
Chapter 1, What Is DAX?
Chapter 2, Using DAX Variables and Formatting
Chapter 3, Building Data Models
Chapter 4, Working with DAX in Power BI, Excel, and SSAS
Chapter 5, Getting It into Context
What is DAX?
In this chapter, you will begin your journey to mastering the use of DAX with a brief introduction to the DAX language itself. We'll look at what it is and why, as a Business Intelligence (BI) professional, you need to learn it if you want to unleash the full power of Excel Power Pivot, Analysis Services, or Power BI.
By the end of this chapter, you will be introduced to the different groups of functions and operators available and the differences between calculated columns and measures, and you will have started your understanding of evaluation contexts. This chapter will round off with a look at how you can alter how filters affect measures by using one of the most important DAX functions, the CALCULATE function.
This chapter is broken down into the following sections:
Introducing DAX
Working with data types and operators
Working with calculated columns and measures
Evaluation contexts – part 1
Using the
CALCULATE
function
Introducing DAX
DAX, or Data Analysis Expressions to give it its full name, is a collection of constants, operators, and functions that are used to build expressions that return one or more values. It was originally developed by the SQL Server Analysis Services team as part of their Project Gemini, the development of a new in-memory database technology that would let Excel users work with massive amounts of data. It was introduced initially in 2009 as an add-in to Microsoft Excel 2010 and eventually went on to become the Power Pivot add-in for Excel that we have today.
Today, DAX consists of more than 250 functions, and regularly receives updates to existing functions as well as receiving new functions. It is a language used by the following products in Microsoft's business intelligence stack:
Excel Power Pivot
Power BI
SQL Server Analysis Services
(
SSAS
) Tabular
Azure Analysis Services
DAX is not a programming language in the traditional sense but is instead a functional language, which means that it makes calls to a function as part of an expression. The result of an expression will, depending on the function, return either a single value or a table as output. The output from an expression can be used to nest functions, by using it as the input parameter to another function.
DAX can only be used to filter or query a physical table; it cannot add, delete, or update data in a table. However, if you are using Power BI or SSAS Tabular, it can use the result of a DAX expression to add a new table to a data model. Unfortunately, this method cannot be used to add tables to an Excel Power Pivot data model without using a workaround, which itself has limitations.
As Power Pivot was originally built as an add-in to Excel, many of the DAX functions are very similar to functions in Excel, which creates a level of familiarity for BI professionals who are already using Excel.
In Table 1-1, you will see that while some functions are almost identical in syntax, others are not. In Excel, the AND function can compare up to 255 logical conditions, while the equivalent function in DAX is limited to just two. Even where functions are identical, the ones in Excel will work with a range of cells, whereas the DAX equivalent will work with columns in a table:
Excel Function
DAX Function
Comments
SUM ( cell range )
SUM ( table[column] )
Excel works with a range of cells; DAX works with the column of a table.
MIN ( cell range )
MIN ( table[column] )
Excel works with a range of cells; DAX works with the column of a table.
MAX ( cell range )
MAX ( table[column] )
Excel works with a range of cells; DAX works with the column of a table.
MEDIAN ( number1, [number2], ... )
MEDIAN ( table[column] )
Excel works with a list of numbers of cells; DAX works with the column of a table.
AND ( logical1, [logical2], ...)
AND ( logical1, logical2 )
Excel supports up to 255 logical conditions; DAX only supports 2 logical conditions.
If you are already working with formulas in Excel, then you will be accustomed to working with cells and ranges of cells. However, if you are to successfully transition to working with DAX, you will need to learn to work with the rows and columns of data in tables.
DAX consists of the following function groups:
Aggregate
Count
Date and Time
Time intelligence
Information
Logical
Mathematical
Statistical
Text
Parent/Child
While DAX functions appear similar to functions found in Excel, they have their own unique characteristics, such as being able to perform calculations that vary by context. They can also return tables as well as values and they can work across the relationships of a data model.
As a BI professional, you may be asking whether it's necessary to learn DAX to be able to use tools such as Power BI or Excel Power Pivot; and the simple answer is no. If you have a well-designed data model filled with good quality data and your reporting requirements are simple, you can get started by dragging and dropping a numeric field onto the report canvas in Power BI, or by adding it to a pivot table in Excel. Behind the scenes, a DAX measure is automatically created, and this is known as an implicit measure.
However, when you want to add columns to existing tables, based on data already in those tables, or you want to create some summary tables, you will probably have to go back to your IT department to get them to add these to an existing database or data warehouse.
The power of DAX is that it enables you, as a BI professional, to add these elements to your data model yourself. Using DAX functions, you can add new columns to an existing table, such as an age range field, based on a person's age.
You can also create explicit measures, which allow you to create aggregated summaries of data, such as record counts. Furthermore, these measures will be dynamically calculated based on any filters or slicers that you add to your Power BI dashboard or Power Pivot worksheet. As you make changes to these filters and slicers, the measures are recalculated dynamically.
With Power BI and Analysis Services, DAX can even be used to create new tables in your data model. Unfortunately, this feature is not available with Excel Power Pivot models.
Quite simply, DAX gives you, as a BI professional, the power to gain deeper insights into your data that you wouldn't otherwise be able to get. When you start to look at the more powerful DAX functions, such as the time-intelligence functions, you can start to carry out some truly amazing analysis of your data. It becomes easy to look at a year-on-year comparison of sales or to look at percentage growth across product ranges for different dates.
While the syntax of DAX is simple, mastering its use can be a challenge. If you are coming from an Excel background, you should be prepared to adopt a different mindset. You will need to study the theory that will be delivered in the following chapters and gain a solid understanding of the following fundamental concepts:
Calculated columns and measures
Context
Syntax
Functions
Each of these will be looked at in detail throughout this book, with plenty of hands-on examples to help you to understand each concept. When you have done this, you will be ready to put what you have learned into practice. Ultimately, the key to truly mastering the art of using DAX is down to lots of practice and experience.
Working with data types and operators
In DAX, you define the data type for columns of data in a table. In this section, we will look at the different data types that are available and delve into the implicit data type conversions that take place when data is used in a DAX expression. We will also look at the different groups of available operators.
Working with calculated columns and measures
Understanding the difference between a calculated column and a measure (also known as a calculated field) is an important concept that you will need to learn to begin mastering DAX. At first, they may seem very similar, and indeed there are some instances where both can be used to obtain the same result. However, they are different and serve different purposes. Likewise, they also impact resources in different ways. Calculated columns allow you to extend a table in your data model by creating additional columns. Measures allow you to aggregate the values of rows in a table and take into account any current filters or slicers that are applied.
Calculated columns
You can create new columns by using DAX expressions if you want to extend a table in your Power BI, Excel Power Pivot, or Analysis Services Tabular data model. These are referred to as calculated columns. In Excel, each row of a column in a worksheet can be defined by using a different expression. However, calculated columns evaluate the same expression throughout the column of a table, calculating the appropriate value on a row-by-row basis.
To create a new calculated column in Power BI Desktop, follow these steps:
Start on the report page and highlight the table that you want to add a new column to from the list of tables shown in the
Fields
pane on the right-hand side.
Right-click on the table name and select
New column
from the menu, as shown in
Figure 1-1
:
Alternatively, you can highlight the table and click on the New Column button on the Calculations section of the Home ribbon or the Calculations section of the Modeling ribbon, as shown in Figure 1-2:
Or you can highlight the table and click on the New Column button on the Calculations section of the Modeling ribbon, as shown in Figure 1-3:
Open the formula editor, and enter the DAX expression that will define your
New Column
.
Figure 1-4
shows the formula editor in Power BI Desktop:
To create a new column in Excel Power Pivot and Analysis Services, we do the following:
Go into your data model and select the table you want to add the new column to.
Select a cell in the last column labeled
Add Column
.
In the formula editor, write the expression that defines your new column.
Figure 1-5
shows the formula editor in Excel. Unlike Power BI,
in Excel Power Pivot and Analysis Services, the
DAX expression begins with the assignment symbol (
) and not the column name:
To rename a new column, once you have entered the expression, right-click on the column name and select
Rename Column
from the menu, as shown in
Figure 1-6
:
Calculated columns are computed during a data refresh and stored in memory with the rest of your data model. This is an important point to note when you are planning and building your data model. On the one hand, with complex expressions, the time taken to compute them is at the point you refresh the data and not when you are querying the data. This can improve the user experience, especially with complex expressions, but you need to remember that each calculated column will take up space in memory. Although this might not be an issue with a smaller table, it could have a significant impact on memory use when you are dealing with large tables. If you have complex expressions behind your calculated columns, then this could also slow down the time it takes to refresh the data in your data model.
You would be well advised not to have too many calculated columns in your data model and to consider whether it would be possible to use a measure instead, especially if it does not impact the user experience too adversely.
