Highline Excel 2016 Class 22: How To Build Data Model ...

[Pages:39]Highline Excel 2016 Class 22: How To Build Data Model & DAX Formulas in Power Pivot

Table of Contents

Which Versions of Excel Contain PowerPivot? ....................................................................................................................... 2 Power Pivot is a COM add-in that you must enable ............................................................................................................... 2 Reminder about Terminology for Tables in a Data Model ..................................................................................................... 2 What is Data Modeling?.......................................................................................................................................................... 2 Power Pivot Data Model's Columnar Database...................................................................................................................... 4 Power Pivot Data Model's DAX Formulas ............................................................................................................................... 5 DAX Calculated Columns......................................................................................................................................................... 5 DAX Measures ......................................................................................................................................................................... 6 Creating Measure in Measure Grid......................................................................................................................................... 7 Implicit vs. Explicit calculations in a PivotTable ...................................................................................................................... 7 DAX Functions seen in this video: ........................................................................................................................................... 8 DAX Calculated Column or DAX Measure to calculate Total Revenue? ................................................................................. 8 Criteria in a Data Model PivotTables ...................................................................................................................................... 8 Calendar Table (Dimension Table) .......................................................................................................................................... 9 Advantage of Power Pivot Data Model Columnar Database & Relationships & DAX Measures when you have Big Data.... 9 Data Modeling Step 1: Power Query to Clean, Transform & Import Fact Tables................................................................. 10 Data Modeling Step 1: Import Dimension Tables from an Excel Sheet ................................................................................ 15 Data Modeling Step 1: Create Calendar Table in Excel & Import to Data Model................................................................. 16 Steps to Create Automatic Calendar Table (Not Seen in Video)........................................................................................... 17 Data Modeling Step 2: Create Relationships between Related Tables ................................................................................ 17 Data Modeling Step 3: Create DAX Calculated Columns in Calendar Table ......................................................................... 18 Data Modeling Step 3: Create DAX Calculated Columns in Fact Table for Revenue: ........................................................... 22 Data Modeling Step 3: Create DAX Measures ...................................................................................................................... 24 Data Modeling Step 3: Alternative Total Revenue Calculation: DAX Measure with SUMX.................................................. 27 Data Modeling Step 3: More DAX Measures ........................................................................................................................ 29 Data Modeling Step 4: Hide Tables & Fields not used in PivotTables .................................................................................. 30 Data Modeling Step 5: Create PivotTables and Pivot Charts ................................................................................................ 31 Data Modeling Step 6: Refresh Data Model when Source Data Changes ............................................................................ 32 Data Modeling Step 7: Fix Calendar Table ............................................................................................................................ 32 Data Modeling Step 7: After Refreshing ............................................................................................................................... 33 Data Modeling Step 7: Create new DAX Formulas and create New Report. ........................................................................ 34 DAX Operators ...................................................................................................................................................................... 36 Cumulative List of Keyboards Throughout Class:.................................................................................................................. 37

Page 1 of 39

Which Versions of Excel Contain PowerPivot?

1) Versions of Excel 2013 contain PowerPivot: ? Office 2013 Professional Plus ? Stand Alone Excel ? Office 365 (E3 or E4 editions)

2) Versions of Excel 2016 contain PowerPivot: ? Office 2016 Professional ? Stand Alone Excel ? Office 365 Professional Plus editions

Power Pivot is a COM add-in that you must enable

1) File, Options, Add-ins, COM add-in, check box for Power Pivot.

Reminder about Terminology for Tables in a Data Model

Examples from data set not seen in this video:

What is Data Modeling?

1) Import Data into Power Pivot Data Model as Proper Data Sets (Tables): ? Using Power Query to Clean, Transform and Import data. ? "Add to Data Model" button in the Power Pivot Ribbon Tab if data is small & is in an Excel Sheet.

2) Create Relationships between Dimension Tables & Fact Tables. 3) Create DAX formulas:

1. DAX Measures to use in Values area of PivotTable. and/or

2. Calculated Columns to use as criteria for Row/Column/Filter/Slicer area of PivotTable or for use in DAX Measure.

4) Hide Tables and Fields that are not used in PivotTables. 5) Create PivotTables & Pivot Charts based on Data Model. 6) Refresh Data Model when source data changes. 7) Edit Data Model as necessary.

Page 2 of 39

Page 3 of 39

Power Pivot Data Model's Columnar Database

1) Power Pivot's Data Model does not store imported tables in in an Excel sheet or in a table format. 2) Power Pivot's Data Model has a behind the scenes Columnar Database where all data is stored. 3) When you import a table into the Data Model, each field in the imported table is stored separately with a unique list of values for the field.

There is a sort of "map" that allows the database to reconstruct the original table and all of the records. 4) The Columnar Database is a behind the scenes In-Memory (RAM) Database.

? RAM = Random Access Memory. ? The number of unique values in any one field determines the amount of RAM that is used. ? The Columnar Database allows you to import large data sets (millions of rows) that would not fit in an Excel sheet. You can safely

handle 100 million rows. 5) The Columnar Database stores data efficiently and can dramatically reduce file size. 6) The Columnar Database is designed to work with DAX Formulas to calculate quickly on Big Data. 7) Example of Columnar Database, where each field is stored in a separate column with a unique list of values only:

Synonyms for Columnar Database:

? Columnar database

? Data Model

? PowerPivot Database stored in an

Excel workbook

? PowerPivot xVelocity engine

? PowerPivot engine

? XVelocity analytics engine

?

? VertiPaq

Page 4 of 39

Power Pivot Data Model's DAX Formulas

1) DAX = Data Analysis Expressions = formulas you can build in Data Model. 2) DAX formulas are specifically designed to work with Columnar Database and Relationships to calculate

efficiently on Big Data. 3) There are many more DAX functions than in a normal PivotTable. We have new functions like RELATED,

SUMX, SAMEPERIODLASTYEAR and CALCULATE. 4) When you create DAX Formulas they appear in PivotTable Field List and can be dragged and dropped

into PivotTable. 5) Convention for creating DAX Formulas:

? When you refer to a Field in a Table use the Table Name & the Field Name enclosed in square brackets (same as Excel Table Formula Nomenclature).

? When referring to a Measure use the Measure Name enclosed in square brackets. 6) Two Types of DAX Formulas:

1. Measures 2. Calculated Columns 7) When you are creating your DAX formula next to the table (Calculated Column) or below the tables (Measures), the DAX formulas must be typed in the Formula Bar.

DAX Calculated Columns

1) "Helper Columns" that are added to the Tables in the Data Model. 2) Calculated Columns can extend the content of the table such as:

? Examples of new fields that extend the content: Month Name or Fiscal Quarter. ? When you have a Calculated Column that extended the table's content, the Calculated Column

will appear in the PivotTable Field List and you can drag and drop into the Row / Column / Filter / Slicer area of a PivotTable. 3) Calculated Columns can be used to calculate numbers such as Revenue, which in turn is used in a DAX Measure. ? This is especially helpful if you have more than 1.04 million rows of records, which cannot fit into an Excel Sheet. By using the Data Model and a Calculated Column, we can easily create a helper column to lookup a price and calculate revenue for each record. 4) DAX Calculated Column formulas: ? Must be create in the Formula Bar above the table. ? Look similar to Excel Table Formula Nomenclature formulas in that they use the Table Name & the Field Name enclosed in square brackets, called field reference or column reference. ? There are no "Cell References" in either Calculated Column or Excel Table Formula Nomenclature.

? When Calculated Columns are calculated/evaluated: 1. Calculated Columns are calculated/evaluated when the column is created or the Data Model is refreshed.

? When you create a Calculated Column, the values are stored in the Column Database in RAM. The more unique values there are, the more RAM used.

? DAX Calculated Columns calculate row-by-row in a Data Model Table using "Row Context" to calculate the answer for each record in the table.

5) Row Context: ? Row Context simply means that field reference (column reference) calculates a different answer for each row based on the data in the row that the formula sits in. For example: for the field reference, "fTransactions[Unit]", the formula knows to get the units for each particular row.

Page 5 of 39

DAX Measures

1) Measures are formulas created to use in: ? The Values area of the Data Model PivotTable. ? Other Measures. ? Sometimes they are used in Calculated Columns.

2) You create or edit Measures in either: ? Measured Grid below Data Model Table. ? Measure dialog box: Power Pivot Ribbon Tab, Calculation group, Measure drop-down arrow, New Measure.

3) DAX Measure formulas: ? Whenever you refer to a field in a Table, called either a column reference or field reference, you use the Table Name & the Field Name enclosed in square brackets, like: fTransactions[Unit]. ? Whenever you refer to another Measure, use the Measure Name enclosed in square brackets. ? Add Number Formatting so that whenever you drag your Measure into a PivotTable the Number Formatting will appear. ? In a PivotTable Field List and in Diagram View, Measures appear with a function icon.

? When Measures are calculated/evaluated:

1. Measures are calculated/evaluated when the formula is dragged into the Values area of a PivotTable or when the criteria is changed or the PivotTable is Refreshed.

? Unlike Calculated Columns, Measures do not store any internal values in RAM. The values are generated when the Measure is dragged into the Values area of a PivotTable or when the criteria is changed or the PivotTable is Refreshed.

? Measures make an aggregate calculation based on the criteria from the PivotTable and/or from inside the formula and calculates an answer for each cell in the PivotTable. The criteria from the PivotTable and/or from inside the formula is called the "Filter Context".

4) Filter Context: ? Filter Context simply means that a Measure can "see" the criteria from the Row/Column/Filter/Slicer area of a PivotTable or from within the formula. The criteria cause the underlying Columnar Database to become "filtered" down to only the records that match the criteria before the final answer is calculated.

5) Advantages of DAX Measures over Standard PivotTable calculations and/or Excel Spreadsheet formulas: ? DAX Measures calculate quickly over millions of rows of data. ? You can create the formula one time and can use it in as many Data Model PivotTables as you want. ? You add Number Formatting to the formula and it follows the formula around. ? There are many new DAX functions like SAMEPERIODLASTYEAR which we don't have in a Standard PivotTable or in an Excel Spreadsheet. ? DAX formulas are easy to edit in one location. When editing is done, all locations where the formula is used are updated. ? DAX Measures, Relationship and the Columnar Database work together to make calculations in the PivotTable quickly.

6) Measures are referred to as "explicit" calculations. 7) NOTE: DAX Measures terminology:

? In Excel 2010 & 2016 Microsoft uses the term "Measure" to refer to DAX formulas that you can use in the Values area of the PivotTable.

? In Excel 2013 Microsoft uses the term "Calculated Field" to refer to DAX formulas that you can use in the Values area of the PivotTable.

Page 6 of 39

Creating Measure in Measure Grid

1) Choose the table in the Data Model whose Field List you want the Measure to appear in. 2) Click in cell below table. 3) Type Measure Name followed by the "assignment operator" := (Colon, Equal Sign). 4) Your cursor will automatically jump up to the Formula Bar. 5) Create formula. 6) Add Number Formatting from the Formatting group in the Manage Data Model Home Ribbon Tab. 7) Example (details later in this project):

?

Implicit vs. Explicit calculations in a PivotTable

1) Implicit DAX Measures ? Drag and drop field into Data Model PivotTable/Power BI Values Area and the Data Model makes a Read Only Measure for you ? MUCH Less Control when you use Implicit 1. Number Field will use SUM Function 2. Text Field uses COUNT 3. You can't add Number Formatting to the Measure 4. You can't Reuse Formula 5. You can't change the name of the Measure 6. If you right-click an Implicit Measure in the Values Area of the PivotTable you can change the "Summarize Values By", but then it adds yet another Implicit Measure

2) Explicit DAX Measure ? You create the formula, choose the name, add the Number Formatting and use it over and over. ? You create formula in Measured Grid, or Measure dialog box ? MUCH More Control when you use Explicit 1. You choose what functions go into your Measure 2. You can name the Measure 3. You can apply Number Formatting that will follow the Measure around when it is reused. 4. You can use the formula over and over. 5. You won't have a bunch of extra Measures created by right-click, "Summarize Values By".

Page 7 of 39

DAX Functions seen in this video:

1) MONTH: Calculates Month Number from Date. 2) FORMAT: Formats a values with a Custom Number Format and converts to text. 3) YEAR: Calculates Year Number from Date. 4) ROUNDUP: Rounds up to a certain digit. 5) IF: delivers on of two items of the same Data Type based on a logical test. 6) ROUND: Standard Rounding rule. 7) RELATED: Looks up an item in a row and through a relationship delivers a related value (like Exact Match

VLOOKUP). 8) SUM: adds numbers. 9) SUMX: iterates a DAX formula over a table, row-by-row (Row Context), & then adds the resultant values. 10) DIVIDE: Can divided two numbers and deliver a DAX BLANK if an error occurs. 11) CALCULATE: Changes the Filter Context for a Measure based on criteria in Filter argument. 12) SAMEPERIODLASTYEAR: Retrieves an amount for same period last year based on the criteria in a Pivot. 13) BLANK: Delivers an empty cell that is not considered text or number and won't interfere with data type.

DAX Calculated Column or DAX Measure to calculate Total Revenue?

1) DAX Calculated Column for calculating revenue for each record in the Fact Table (we see how to create this later in the project). Example demonstrated later in the prject:

=ROUND(RELATED(dProducts[Retail Price])*(1-fTransactions[Revenue Discount])*fTransactions[Units],2)

? DAX Calculated Column for Revenue stores the column's unique values in the Columnar Database: 1. If there are a few unique values, not much RAM space used 2. If there are many unique values, more RAM space used.

? DAX Calculated Columns actually calculate an answer for each record in the column when the Calculated Column is created or when the Data Model is Refreshed.

2) DAX Measure for Total Revenue (we see how to create this later in the project). Example demonstrated later in the prject:

=SUMX(fTransactions,ROUND(RELATED(dProducts[Retail Price])*(1-fTransactions[Revenue Discount])*fTransactions[Units],2))

? DAX Measure does NOT store the values in RAM ? DAX Measure gets calculated only when you drop it into PivotTable OR if you change the criteria

in the Row / Column / Filter / Slicer area. It is calculated by CPU ? Central Processing Unit. 3) Which one to use?

? It depends in part on how many unique values there are. ? If Data Model is working slow, you may need to test which one works more quickly.

Criteria in a Data Model PivotTables

1) If you have a choice between a field that is in both a Dimension Table and Fact Table, Drag criterion from the Dimension Tables to the Row/Column/Filter/Slicer area of the PivotTable.

2) Using Criteria from Dimension Tables rather than Fact Tables helps the DAX Formulas to calculate more quickly.

Page 8 of 39

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download