Learn to Write DAX: A Practical Guide to Learning Power Pivot for Excel and Power BI - Softcover

Allington, Matt

Softcover

ISBN 10: 1615470417 ISBN 13: 9781615470419

Verlag: Holy Macro! Books, 2015

Inhaltsangabe

Active learning lessons for mastering DAX
 
Data analysis expressions (DAX) is the formula language of PowerPivot and this book is written to give hands-on practice to anyone who wants to become competent at writing such formulas. Sample exercises that explain each concept are provided and followed by practice questions and answers to maximize learning and experience with DAX.

Über diesen Titel

Über die Autorin bzw. den Autor

Matt Allington has worked in the retail and consumer packaged goods industries for more than 35 years in both commercial and information technology roles. He is the former business intelligence director for the Coca-Cola Company in Asia Pacific where he led the adoption of integrated SharePoint/Excel dash boarding tools.

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

Learn to Write DAX

A Practical Guide to Learning Power Pivot for Excel and Power BI

By Matt Allington

Holy Macro! Books

Copyright © 2016 Tickling Keys, Inc.
All rights reserved.

ISBN: 978-1-61547-041-9

Contents

1: Concept: Pivot Tables vs. Power Pivot, 3, 
2: Concept: Loading Data, 5, 
3: Concept: Calculated Fields, 24, 
4: DAX Topic: SUM(), COUNT(), COUNTROWS(), MIN(), MAX(), COUNTBLANK(), and DIVIDE(), 34, 
5: Concept: Filter Propagation, 51, 
6: DAX Topic: The Basic Iterators SUMX() and AVERAGEX(), 57, 
7: DAX Topic: Calculated Columns, 66, 
8: DAX Topic: CALCULATE(), 69, 
9: Concept: Evaluation Context and Context Transition, 76, 
10: DAX Topic: IF(), SWITCH(), and FIND(), 80, 
11: DAX Topic: VALUES() and HASONEVALUE(), 83, 
12: DAX Topic: ALL(), ALLEXCEPT(), and ALLSELECTED(), 90, 
13: DAX Topic: FILTER(), 103, 
14: DAX Topic: Time Intelligence, 112, 
15: DAX Topic: RELATED() and RELATEDTABLE(), 135, 
16: Concept: Disconnected Tables, 139, 
17: Concept: KPIs and Multiple Data Tables, 149, 
18: Concept: Cube Formulas, 160, 
19: Moving from Excel to Power BI, 166, 
20: Next Steps on Your DAX Journey, 173, 
Appendix A: Answers to Practice Exercises, 175, 
Index, 182, 

CHAPTER 1

Concept: Pivot Tables vs. Power Pivot

Some people wonder what the difference is between Power Pivot and pivot tables, so I'm going to start by explaining. Read on, and you'll have it sorted out in no time.

What Is a Pivot Table?

A pivot table is a summarisation and visualisation tool. The job of a pivot table is to connect to a data source and create on-the-fly totals and subtotals to help you and others make sense of data. The larger the set of data and the more granular the data, the more useful a pivot table becomes. Because pivot tables are embedded right inside Excel, with them you get all the other benefits of Excel as well.

Data Sources for Pivot Tables

Historically, there have been two main types of data sources that you can connect to with a pivot table: flat tables and data cubes.

Connecting to a Single Flat Table

To connect to a single flat table inside Excel, click in the table, select Insert, Pivot Table, and off you go. There are some limitations with this approach, however:

• It is very common to have to do a lot of VLOOKUP()s (or similar operations) to be able to join data from different data sources into a single flat table.

• Excel has a 1 million row limit. In fact, though, if you are using lots of VLOOKUP()s in a single flat table, you will reach performance limits well before you ever hit 1 million rows.

These two issues have historically prevented Excel from being a scalable BI tool. But Power Pivot changes that, as you'll see in a few moments.

Connecting to a Data Cube

A less common but very powerful use of pivot tables is to connect directly to a reporting cube such as a SQL Server Analysis Services multidimensional cube directly from Excel. Many large enterprises have multidimensional data cubes available for reporting. Allowing Excel users to connect directly to a cube and use a pivot table for reporting is super easy and convenient. But this is a relatively rare use case compared to the general use of Excel and the more common single-table use of pivot tables.

Enter Power Pivot

Power Pivot doesn't change anything about pivot tables, but it changes everything when it comes to the data that pivot tables connect to. Power Pivot adds a third (and, in my view, the best) method of connecting to source data.

Power Pivot is a data modelling tool that is used to structure and extend source data so that it can be analysed using Excel pivot tables (among other tools). Data modelling is not a term that is often familiar to Excel users as it is normally the domain of IT BI professionals. But this is no longer the case with Power Pivot for Excel.

What Is Data Modelling?

Data modelling is the process of taking data from various sources; loading, structuring, and relating data logically to other data; and enhancing, embellishing, and generally preparing the data for use.

The data modelling process includes:

• Determining the optimal structure and shape of the source data to analyse (e.g., whether to bring in all the data, full data, or summary data).

• Loading the data from the source into the data model (Power Pivot in this case).

• Defining the logical relationships between the various tables (which is similar to what you do with VLOOKUP() inside Excel).

• Defining data types (e.g., specifying whether a column of data is numeric or a column of currency values or a column of text fields).

• Creating new insights from the source data so that you can analyse concepts that don't exist natively in the source data but yet can be calculated or created inside the data model. For example, if you have a table of transactional data with cost price and sell price, you can extend the data model to include a calculation for margin, margin percentage, etc., even though these concepts are not explicitly in the source data. Once you have modelled these new facts in the data model, they can be reused over and over by people using your workbook.

When you learn Power Pivot, you are actually learning data modelling. The term can be a little bit scary, but there is no reason to be concerned. By the time you have finished this book, you will be well on your way to being an accomplished data modeller using Power Pivot. Just use the techniques covered in this book and keep in mind that what you are actually doing is learning to be a data modeller using Power Pivot.

CHAPTER 2

Concept: Loading Data

Before you can start to write DAX and use Power Pivot, you need to load some data. Power Pivot always loads a complete copy of the source data into the Data Model as the first step in the process. Once it's loaded, you can share your workbooks with others, and there is no need for anyone else to have direct access to your source data.

When you load data, you have to decide which data to import, including which tables, which columns in each table, and also what "shape" the data should be when imported. In the following section, you will simply load data that has been prepared for you. But you need to be aware that the process of deciding which data to import is an important part of the data modelling process — and it has been done for you in this case.

Here's How: Loading Data from a New Source

You can download a copy of the sample AdventureWorks database used in this book from http://xbi.com.au/learndax. You should download the database now, unzip it, and place it in a location that is easy for you to find.

You are going to start off by loading the following tables from the AdventureWorks Access database:

• Sales

• Products

• Territories

• Calendar

• Customers

Then you will prepare these tables for use in Power Pivot.

Follow these steps to load data into a workbook for use in Power Pivot:

1. Open a new blank Excel workbook. You should see the PowerPivot tab at the top of the sheet.

2. If you don't see the PowerPivot tab, select File, Options, Add-Ins. Then scroll to the bottom of the window and select COM Add-ins from the Manage list. Then click Go.

3. In...

Details zur Ausgabe

• Verlag: Holy Macro! Books
• Erscheinungsdatum: 2015
• Sprache: Englisch
• ISBN 10: 1615470417
• ISBN 13: 9781615470419
• Einband: Taschenbuch
• Anzahl der Seiten: 184
• Kontakt zum Hersteller: Nicht verfügbar
• Verantwortliche Person: Nicht verfügbar