How to Build Data Model and DAX Formulas in Power Pivot

Dive into an extensive Excel 2016 tutorial focused on building data models and creating DAX formulas in Power Pivot. Learn the step-by-step process of data modeling, from importing and cleaning data using Power Query to creating relationships between tables. Master DAX functions like MONTH, FORMAT, YEAR, ROUNDUP, and IF to create calculated columns in calendar tables. Explore the concepts of row context and filter context, and understand when to use DAX calculated columns versus DAX measures. Discover how to create PivotTables, Pivot Charts, and build a final dashboard. Gain insights into the advantages of Power Pivot's columnar database for handling big data. Practice refreshing data models, fixing calendar tables, and creating new DAX formulas for advanced reporting. This comprehensive guide covers everything from basic data modeling steps to advanced DAX techniques, providing a solid foundation for Excel power users and aspiring data analysts.

) Introduction to Entire Project, including what type of start files we have and what the final report looks like..
) What is Data Modeling? Steps to creating a Data Model..
) Data Modeling Step 1: Power Query to Clean, Transform & Import Fact Tables.
) Data Modeling Step 1: Import Dimension Tables from an Excel Sheet.
) What is a Calendar Table (Dimension Table)? Why a Calendar Table and not Group by Date?.
) Data Modeling Step 1: Create Calendar Table in Excel & Import to Data Model.
) Data Modeling Step 2: Create Relationships between Related Tables.
) Data Modeling 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..
) What is Row Context?.
) Data Modeling Step 3: Create DAX Calculated Columns in Fact Table for Revenue. See the functions ROUND, RELATED .
) Data Modeling Step 3: Create DAX Measures using SUM function n to add values from Calculated Column..
) Data Modeling 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..
) DAX Calculated Column or DAX Measure to calculate Total Revenue?.
) Data Modeling Step 3: More DAX Measures. SUMX to calculate Total COGS. DAX Measure for Gross Profit..
) Data Modeling Step 4: Hide Tables & Fields not used in PivotTables.
) Data Modeling Step 5: Create PivotTables and Pivot Charts and Final Dashboard.
) What is Filter Context?.
) Advantage of Power Pivot Data Model Columnar Database & Relationships & DAX Measures when you have Big Data..
) Data Modeling Step 6: Refresh Data Model when Source Data Changes.
) Data Modeling Step 7: Fix Calendar Table.
) Data Modeling Step 7: Dashboard After Refreshing.
) Data Modeling Step 7: Create new DAX Formulas and create New Report..