Supercharge Excel: When you learn to Write DAX for Power Pivot - Softcover

Allington, Matt

Softcover

ISBN 10: 1615470530 ISBN 13: 9781615470532

Verlag: Holy Macro! Books, 2018

Inhaltsangabe

Data analysis expressions (DAX) is the formula language of Power Pivot. Learning the DAX language is key to empower Excel users so they can take advantage of these new Business Intelligence (BI) capabilities. This volume clearly explains the concepts of Power Pivot while at the same time offering hands-on practice to engage the reader and help new knowledge stick. This second edition has been updated for the Excel 2016 user interface while still providing a bridge for readers wanting to learn DAX in the Excel environment and then transfer their new DAX skills across to Power BI.

Über diesen Titel

Über die Autorin bzw. den Autor

Matt Allington is a Microsoft Data Platform MVP that specializes in teaching and helping business users leverage the Power BI suite to achieve positive business outcomes.

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

Supercharge Excel

When You Learn to Write DAX for Power Pivot

By Matt Allington

Holy Macro! Books

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

ISBN: 978-1-61547-053-2

Contents

Supercharge Excel, 
Introduction, 
1: Concept: Introduction to Data Modelling, 
2: Concept: Loading Data, 
3: Concept: Measures, 
4: DAX Topic: SUM(), COUNT(), COUNTROWS(), MIN(), MAX(), COUNTBLANK(), and DIVIDE(), 
5: Concept: Filter Propagation, 
6: Concept: Lookup Tables and Data Tables, 
7: DAX Topic: The Basic Iterators SUMX() and AVERAGEX(), 
8: DAX Topic: Calculated Columns, 
9: DAX Topic: CALCULATE(), 
10: Concept: Evaluation Context and Context Transition, 
11: DAX Topic: IF(), SWITCH(), and FIND(), 
12: DAX Topic: VALUES() and HASONEVALUE(), 
13: DAX Topic: ALL(), ALLEXCEPT(), and ALLSELECTED(), 
14: DAX Topic: FILTER(), 
15: DAX Topic: Time Intelligence, 
16: DAX Topic: RELATED() and RELATEDTABLE(), 
17: Concept: Disconnected Tables, 
18: Concept: KPIs, 
19: Concept: Multiple Data Tables, 
20: Concept: Cube Formulas, 
21: Moving from Excel to Power BI, 
22: Next Steps on Your DAX Journey, 
Appendix A: Answers to Practice Exercises, 
Index, 

CHAPTER 1

Concept: Introduction to Data Modelling

The data modelling engine that is used inside Power Pivot for Excel is the same one used in Power BI. Data modelling is not a term that is often familiar to business users as it is normally the domain of IT BI professionals. But this is no longer the case, thanks to the introduction of Power Pivot for Excel and Power BI.

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 objective is to be able to use the data without having to write a custom query every time you want to look at a different subset of data.

The data modelling process includes:

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

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

• Defining the logical relationships between the various tables (which is similar to what you do with VLOOKUP() in Excel, except the data stays in the source table in Power Pivot).

• 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 that 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 calculations 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.

• Giving meaningful names to your new business insights (i.e., to your measures).

When you learn the DAX language and join your tables of data in Power Pivot for Excel, you are actually learning data modelling. The term data modelling 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. Having said that, however, I should also point out that there is a big difference between being able to do it and being an expert. Becoming an expert takes years of practice, experience, and lifelong learning.

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, PivotTable, 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. Excel has limited ability to manage large sets of data for reporting purposes, whereas Power Pivot has no theoretical database size limit.

Power Pivot is a data modelling tool that allows you to prepare your data in a way that pivot tables can use. Data modelling is the process of preparing data so it can be used in reporting tools (such as a pivot table) without the need to write new database queries every time.

Power Pivot is a Microsoft SQL Server Analysis Services tabular database that is bundled with Microsoft Excel via a COM add-in. Excel manages Power Pivot databases, so the experience is seamless and transparent to the end user. You can use a user interface in Excel to build Power Pivot databases directly inside Excel.

Power Pivot allows you to:

Import data from many different data sources.

Logically join separate tables of data together so the data works together without the need for VLOOKUP() formulas.

Enhance the underlying raw data so that you can create new derived concepts (measures) from that data. For example, if the source data has sell price and cost price, it is...

Details zur Ausgabe

• Verlag: Holy Macro! Books
• Erscheinungsdatum: 2018
• Sprache: Englisch
• ISBN 10: 1615470530
• ISBN 13: 9781615470532
• Einband: Taschenbuch
• Auflage: 2
• Anzahl der Seiten: 205
• Kontakt zum Hersteller: Nicht verfügbar
• Verantwortliche Person: Nicht verfügbar