# Highline Excel 2016 Class 22

## How To Build Data Model & DAX Formulas in Power Pivot

### DESCRIPTION

(00:16) Introduction to Entire Project, including what type of start files we have and what the final report looks like.
(03:36) What is Data Modelling? Steps to creating a Data Model.
(05:07) Data Modelling Step 1: Power Query to Clean, Transform & Import Fact Tables
(11:22) Data Modelling Step 1: Import Dimension Tables from an Excel Sheet
(12:23) What is a Calendar Table (Dimension Table)? Why a Calendar Table and not Group by Date?
(15:14) Data Modelling Step 1: Create Calendar Table in Excel & Import to Data Model
(17:45) Data Modelling Step 2: Create Relationships between Related Tables
(19:30) Data Modelling Step 3: Create DAX Calculated Columns in Calendar Table. Dee the DAX functions: MONTH, FORMAT, YEAR, ROUNDUP, and IF. See how to calculate Calculated Columns for Month Number, Month Name, Year, Quarter, Fiscal Quarter, Fiscal Year and Fiscal Period.
(21:40) What is Row Context?
(30:45) Data Modelling Step 3: Create DAX Calculated Columns in Fact Table for Revenue. See the functions ROUND, RELATED
(34:02) Data Modelling Step 3: Create DAX Measures using SUM function n to add values from Calculated Column.
(36:33) Data Modelling Step 3: Alternative Total Revenue Calculation: DAX Measure with SUMX. Learn how to perform Row Context in a DAX Measure. This formula calculates total revenue without a Helper Column.
(39:18) DAX Calculated Column or DAX Measure to calculate Total Revenue?
(40:45) Data Modelling Step 3: More DAX Measures. SUMX to calculate Total COGS. DAX Measure for Gross Profit.
(44:25) Data Modelling Step 4: Hide Tables & Fields not used in PivotTables
(46:15) Data Modelling Step 5: Create PivotTables and Pivot Charts and Final Dashboard
(47:41) What is Filter Context?
(47:41) Advantage of Power Pivot Data Model Columnar Database & Relationships & DAX Measures when you have Big Data.
(53:55) Data Modelling Step 6: Refresh Data Model when Source Data Changes
(54:49) Data Modelling Step 7: Fix Calendar Table
(55:50) Data Modelling Step 7: Dashboard After Refreshing
(56:11) Data Modelling Step 7: Create new DAX Formulas and create New Report.