Supercharge Power BI E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 396

Veröffentlichungsjahr: 2024

Beschreibung

This guide is designed to empower Power BI users with advanced skills in data modeling and DAX. It begins with an introduction to the foundational concepts of data modeling, where you'll learn how to structure your data for optimal performance and analysis. You’ll then progress to mastering essential DAX functions, including iterators, filters, and time intelligence. These chapters will help you create sophisticated calculations that bring your data to life.
As you advance, the guide delves into more complex topics like evaluation context, context transition, and disconnected tables. These concepts are crucial for understanding how DAX formulas interact with your data, enabling you to build more accurate and insightful reports. The guide also covers practical applications, such as transferring DAX skills to Excel and using advanced Power BI features like Analyze in Excel and Cube Formulas.
By the end of this book, you'll have a deep understanding of both data modeling and DAX, equipping you with the knowledge to tackle complex data challenges. Whether you’re working on business intelligence projects or enhancing your data analysis capabilities, this guide will give you the tools to excel in Power BI.

Leseprobe

Inside Front Cover - This page intentionally blank

Supercharge Power BI

Power BI Is Better When You Learn to Write DAX

3rd Edition

by

Matt Allington

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32954

Supercharge Power BI - 3rd Edition

© 2021 Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an "as is" basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Author: Matt Allington

Layout: Jill Bee

Copyediting: Kitty Wilson

Cover Design: Emrul Hasan, Shannon Travise & Maddie Allington

Cover Illustration: Freepik

Indexing: Nellie Jay

Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32954, USA

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: January, 2021.

ISBN: 978-1-61547-069-3 Print, 978-1-61547-155-3 e-Book

Library of Congress Control Number: 20211931632

Table of Contents

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: Calculated Columns

8: DAX Topic: The Basic Iterators SUMX() and AVERAGEX()

9: DAX Topic: CALCULATE()

10: DAX Topic: Making DAX Easy to Read

11: Concept: Evaluation Context and Context Transition

12: DAX Topic: IF(), SWITCH(), and FIND()

13: DAX Topic: VALUES(), HASONEVALUE(), SELECTEDVALUE(), and CONCATENATEX()

14: DAX Topic: ALL(), REMOVEFILTERS(), ALLEXCEPT(), and ALLSELECTED()

15: DAX Topic: FILTER()

16: DAX Topic: Time Intelligence

17: DAX Topic: Variables in DAX

18: DAX Topic: RANKX() and TOPN()

19: DAX Topic: RELATED() and RELATEDTABLE()

20: Concept: Disconnected Tables

21: Concept: Multiple Data Tables

22: Concept: Using Analyze in Excel and Cube Formulas

23: Concept: Transferring DAX Skills to Excel

24: Concept: Next Steps on Your DAX Journey

Appendix A: Answers to Practice Exercises

Appendix B - DAX Quick Reference List

Index

1: Concept: Introduction to Data Modelling

In the past, the term data modelling was often unfamiliar to business users as data modelling tended to be the domain of IT BI professionals. But this is no longer the case, thanks to the introduction of self-service BI tools such as Power BI and 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 objective is to allow the data to be used 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, a subset of the data, or summary data.Loading the data from the source into the data model (Power BI 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 BI).Defining data types (e.g., specifying whether a column of data is a column of decimal values or a column of currency values, a column of text, etc.).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 selling 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).

Power BI, Power Pivot, and SSAS Tabular

The data modelling engine that is used inside Power BI is the same one used in Power Pivot for Excel and SQL Server Analysis Services (SSAS) Tabular. The engine has quite a few names, including xVelocity, VertiPaq, SSAS Tabular, and Power Pivot. Despite the different names, the engine is essentially the same across all of these software products (with the exception of any version differences related to version release timing). It therefore follows that virtually everything you learn in this book about data modelling and DAX can be applied to Power Pivot for Excel and SSAS Tabular. In this book I most commonly refer to Power BI, but you should keep in mind that most of the content also applies to these other products.

Remember: Power BI Is a Database Tool, and Excel Is a Spreadsheet Tool

One very important concept you simply must understand is that Power BI is a database tool, and Excel is a spreadsheet tool. A database and a spreadsheet are not the same. A database has structure; it consists of one or more tables of data. Each table has zero, one, or more rows of data and one or more columns. Each column has a defined data format, and the data in each column must conform to that format. A database does not have a column-and-row reference system. A spreadsheet (e.g., an Excel worksheet) lacks the structure of a database. A spreadsheet, like a database, has columns and rows, but these columns and rows lack structure. You can put any data you want into any cell in a spreadsheet, and you can refer to a cell by using a cell reference. You have to follow a lot of rules with a database that don’t apply to a spreadsheet.

Remember that Power BI is not Excel. You need to think differently if you are to master Power BI than you need to think to master Excel.

Power BI Is a Data Model–Based Tool

Power BI is a data model–based BI reporting tool. Not all BI tools are data model based. One example of a non-data model–based BI tool is SQL Server Reporting Services (SSRS). Non-data model–based BI reporting tools require the report writer to first generate a query to fetch the data from a database (typically SQL Server) and return the results of that query to SSRS so the results can be rendered in a report. With a non-data model–based reporting tool, you can typically use a user interface that helps with the generation of the query, or you can use a scripting language such as T-SQL to fetch the data that you need for each report.

Traditional Excel—that is, the spreadsheet tool without the modern BI add-ins of Power Query and Power Pivot—is also a non-data model–based BI tool. In the case of traditional Excel, the user loads data into the spreadsheet and then logically relates and aggregates the data using Excel formulas and builds a report (often on a new sheet) to summarise and present the results (the report).

Now don’t confuse “a tool” here with “a data model–based tool.” Excel is definitely a tool; it is a very flexible tool that lets you build virtually any report without being a programmer. In fact, I think Excel is probably the best and most popular BI tool ever invented. But it is not a data model–based tool because traditional Excel doesn’t have a data model.

There is nothing wrong with using a non-data model–based BI tool; it is just a different approach from using a data model–based tool. The biggest issue with non-data model–based reporting tools is that every time you need a new report, you have to start again, often from scratch. Each report has a single purpose, and there is very little reusability or extensibility.

A data model–based tool like Power BI has many benefits, including the following:

The author of the data model builds a reusable model that can be used to solve the current reporting requirements as well as (often) future requirements without the need to write further queries to retrieve a new subset of data.The author can often be a business user (normally with good Excel skills) and doesn’t have to be a professionally trained database administrator or SQL professional.The data model is conceptual in nature, supported by a user-friendly interface that lets you build the data model logically, with minimal coding. Keep in mind that you do need to do some coding (formula writing) for a good data model, but it is fairly easy—no harder than building a typical spreadsheet in Excel—when you have the skills, as you will see later in this book.

The term data modelling can be a little bit scary, but there is no reason to be concerned. When you learn the DAX language and join your tables of data in Power BI, you are actually learning data modelling. By the time you have finished this book, you will be well on your way to being an accomplished data modeller using Power BI. 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.

With all this in mind, it’s time to build your first simple data model.

2: Concept: Loading Data

The first step in data modelling is to load data into Power BI Desktop. The image below shows the data connector that appears when you connect to a SQL Server database from Power BI Desktop. (There is a different data connector for each data source. You’ll see how to get to the various data connector screens later in this chapter.) There are two modes that you can use in Power BI Desktop when loading data from a database tool such as SQL Server: Import and DirectQuery.

Most data sources do not provide these two options and instead only allow you to use Import mode. This book focuses on Import mode. When you use Import mode, Power BI Desktop loads a complete copy of the source data into the data model as the first step in the process. Once it is loaded, you can share your .pbix workbook with others, and there is no need for anyone else to have direct access to the source data. Alternatively, you can publish your reports to PowerBI.com and share the contents with others from there. When you publish a report to PowerBI.com, a complete copy of the data is loaded into the cloud also, without the need for it to access the source data. The data that is loaded into your data model retains a connection to the original source. It is a simple task to refresh the data when the data at the source changes. Such a refresh can either be triggered manually or set to automatically occur on a schedule.

When you load data into Power BI Desktop, you have to decide which data to load, including which tables, which columns in each table, etc. I call this the shape of the data. The following “Here’s How” shows how to load data that has been prepared for you. But you need to be aware that the process of deciding which data to load is an important part of the data modelling process, as discussed later in this chapter.

Here’s How: Loading Data from a New Source

If you don’t already have a copy of the custom version of the AdventureWorks database used in this book, you should download the sample Excel file now (from http://xbi.com.au/sample-db), unzip it, and place it in a location that is easy for you to find. In the following steps, you will load the following tables from the AdventureWorks database:

Sales

Products

Territory

Calendar

Customers

For convenience and ease of access, I have extracted all the relevant data from the AdventureWorks database into individual sheets inside an Excel workbook; that is what you will find inside the .zip file. The following steps show you how to load these database tables from the Excel workbook and prepare them for use in Power BI:

1.Open Power BI Desktop. You should see a blank Power BI Desktop file with a ribbon along the top, as shown below.

2.From the Home tab in Power BI Desktop, select Excel (see #1 above). Alternatively, select Get Data (#2 above), All, Excel (#1 below) and then click Connect (#2 below).

3.Browse to the location of the sample Excel file you downloaded earlier and click Open.

4.When Power Query connects to the file, you see a list of all objects that can be imported into Power BI. In this instance, you see a list of sheets inside Excel that contain data that can be loaded. You need to select the five sheets that contain the tables of data needed for this book, as shown below. At this point, you should not load the other sheets of data.

Tip: The sample data for this book has been well prepared for learning how to use Power BI. You should not assume that a source database has the correct table structure for Power BI; few of them do. In most cases, you need to transform data as described in the following note.

Note: If you now click the Transform Data button shown above, you will launch the Query Editor (in Power Query), where you can transform the data prior to loading it into Power BI Desktop. Power Query is a tool in its own right and has its own language, called M, that facilitates data cleaning and shaping to prepare the data for use. Learning about Power Query is beyond the scope of this book, but I have a comprehensive online training course specifically designed to teach you how to use this powerful tool. You can learn more about that training course at http://xbi.com.au/pq-training.Notice that two of the tables listed above have names that start with the prefix dim (short for dimension): dimProductCategory and dimProductSubCategory. In database design, it is very common for tables to have prefixes like this. fct (short for fact) is another example of a table prefix. It is best practice to remove any such prefixes from table names if they exist in the source data. These prefixes have meaning to IT folk and help identify the type of table, but given that these table names will be visible to business users who use your Power BI reports, it is best to remove the prefixes after import by simply right-clicking a table and renaming it (or by renaming the tables during loading from within Power Query).

5.Click Load, and Power BI Desktop loads your data. After the Table Import Wizard is closed, you see the five tables you have just imported in Power BI on the right side, as shown below. Each of the tables is a complete copy of the data you imported from the source files (an Excel workbook, in this example). You don’t need the source files again until you are ready to refresh the data—typically when the data changes at some time in the future. This is one of the many great things about Power BI: You can simply refresh the data when the data changes, and your workbooks are updated with the new data.

6.Switch to the Data view by clicking the Data icon (see #1 above). You can now see the data in the tables, as shown in the image below.

7.In the Data view, double-click the Territory table name on the right, as shown below, and rename it Territories for consistency (e.g., naming all tables with plurals—except Calendar, of course).

8.For the next stage of the data modelling process, which involves creating the logical relationships between the tables, switch to the Model view by clicking the Model icon (see #1 below).

9.If you can’t see all five tables on the screen, click the Fit to Screen button (#1 below). You can also collapse the tables (#2, #3, and #4) to save space in this view.

Note: You do not need to see all the columns in the Model view. The purpose of this view is to show how the tables are related. If you want to see the column names, switch to the Data view. Trust me.

In the image shown below, two of the tables have automatically been joined. Power BI, by default, automatically attempts to logically join the tables of data together. These automatically created relationships may or may not be correct; in this case, they are indeed correct.

10.If desired, change the layout of these tables by dragging and dropping. As you will learn later in this chapter, I recommend positioning the tables so that any transactional tables (there is only one, Sales, in this case) are at the bottom of the screen and the lookup tables are at the top.

When you’ve completed these steps, you need to create relationships between the Sales table and the other four tables. You’ll learn how to do so in the next “Here’s How” section.

Other Data Sources

In this book, I teach you how to import data from the AdventureWorks Excel sample file, but this file is only an example of the many data sources that you will likely need as you build your own reports. There are many other data source connectors available in Power BI (though technically they are part of Power Query). To see a full list, simply click on Get Data, More, All. You can then see the full list of currently supported data connectors in Power BI. The screenshot below shows some of the connectors in this list.

The general process for importing data is the same for any data source you use: You simply select the appropriate data source and then follow the import wizard just as shown earlier in this chapter, under "Here’s How: Loading Data from a New Source" on page 9.

Here’s How: Creating Relationships in Power BI Desktop

The Introduction provides a high-level overview of the AdventureWorks database. Here we look more closely at some of the tables in that database. The Sales table holds records of sales transactions. It has the following features:

One row of data is generated for each item scanned at the cash register and stored in the Sales table.There can be many rows of data in the Sales table that together make up a single customer invoice.Each customer can shop many times, so there may be more than one invoice for each customer in the Sales table.The date, the customer number, the product codes, the quantity, the cost price, and the selling price of each item sold (among other data) are stored in the Sales table.Only the absolute minimum amount of data needed to identify the customer, the product, the time period, and the store are recorded in the Sales table. This data is presented in “key” columns, such as Customer Key (e.g., customer number) and Product Key (e.g., product number).

Note: As described later in this chapter, I call the transaction tables data tables and the other tables lookup tables.

Each transaction in the Sales table contains a customer key that uniquely identifies which customer purchased each item. All the other information about that customer (e.g., name, address) is stored in the Customers table. When using traditional Excel, it is common to write a VLOOKUP() formula to fetch the name of the customer who purchased each item and bring it back to the Sales table. This is not required in Power BI. Instead, the two tables of data (the Sales table and the Customers table) can be logically related using the common customer key that exists in both tables. When these tables are joined using the customer key, there is a one-to-many (Customers to Sales) relationship between these two tables. Exactly the same principle applies to the Products, Calendar, and Territories tables.

To join a lookup table to a data table in Power BI Desktop, follow these steps:

Note: