Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model)

Create your first Power Pivot model across Excel 2013/2016, Excel 2010, and SQL Server SSDT, loading data into the data model from text files such as Adventureworks internet sales.

Create your first Power Pivot model in Excel, connect it to a pivot table, choose fields like sales amount and due date, and explore drill-down and formatting options.

Create and deploy your first data model in Excel 2010 by importing a text file into the Power Pivot window, building a pivot table, and using fields, slicers, and grouping.

Import and model data from SQL Server to create a three-table PowerPivot view, linking product subcategory to product and sales, then build a pivot table to summarize total sales.

Install and configure SQL Server Developer 2022 via the installation center, selecting Developer Edition and necessary features, set authentication and instance configuration, and assign administrator access.

install visual studio 2022 on 64-bit windows 10/11, meet hardware requirements, and select data storage and processing workloads for SQL Server. access 2019 downloads if needed, using community edition.

Install the SSAS extension for Visual Studio 2022 from the marketplace, then close and reopen Visual Studio to create MDX analysis and Analysis Services multidimensional or tabular projects.

Learn how to license Visual Studio Community Edition without it being a trial by updating the license and signing in with a Microsoft account.

Download AdventureWorksDW data from the official Microsoft site or GitHub, then organize the files in an SSAS files folder for use with Power Pivot and SSAS.

Import and configure data from a SQL Server, AdventureWorks DW, into SSAS, creating a tabular model in Visual Studio and exporting to Excel for analysis.

Add a second table from the same source, import dim product alongside fact internet sales, and drag to create a one-to-many relationship in diagram or model view.

Import two tables from SQL Server and a text file, join them with product subcategory and product category keys, and create a sum of sales amount measure for category pivot.

Import data into SSAS, connect dim product subcategory and product category tables, and analyze in Excel with pivot tables showing sales amounts.

Learn to fix Excel import issues in the Visual Studio tabular model by installing the 2007 data connectivity components and the Microsoft Access Database Engine, addressing 64/32-bit conflicts.

Learn how to filter, sort, and format fields in the data model, including temporary filters, currency and date formats, and automatic updates across Power Pivot and SSAS.

Explore how PowerPivot and SSAS automatically generate implicit measures, and how to create explicit measures such as sum and max using sigma auto sum and pivot tables.

Perspectives tailor a model for different audiences by hiding tables and columns; English and French views show the concept. Choose to load into SSAS tabular or SharePoint to enable perspectives.

Create and order hierarchies in Power Pivot (Excel) and SSAS tabular DAX model by dragging fields into a single-column hierarchy; hide originals to avoid clutter.

Explore pivot table structure with filters, rows, columns, and values. Learn to apply subtotals, grand totals, and include filtered items in totals to show detail versus overall totals.

Learn to create running totals and cumulative sales by year in Power Pivot tables using value field settings, with formatting to highlight zero years and simplify analysis.

Explore slicers and timelines in Excel to filter pivot tables and the Power Pivot model, connect them to multiple pivot tables, and build an interactive dashboard with charts.

Convert a pivot table to formulas using OLAP tools to query the data model with cube value, extracting measures such as sum of sales amount without pivot tables.

Explore dax functions for Power Pivot and ssas tabular, including over 220 functions with new 2016 additions, and learn to apply them to imported data and hierarchies.

Create calculated columns to convert sales from pounds to dollars using an exchange rate, add and name the column, and relate it across tables in Power Pivot and SSAS.

Learn how and, or, and not operate in dax for power pivot and ssas, using nested or conditions to handle cases like less than five or greater than ten.

Use a DAX switch function to map sales territory keys to descriptions (northwest, northeast, central, southeast, southwest, outside the US), replacing lengthy if statements.

Explore how measures enable aggregations across SSAS tabular and Excel Power Pivot, distinguish them from calculated fields and columns, and use Autosum and sigma to create sum and average aggregates.

Explore standard aggregations—sum, count, count A, average, max, min, and median—in Power Pivot and SSAS, and learn to sum expressions via calculated columns and measures.

Learn how to use distinct count in PowerPivot and SSAS tabular data models to count unique sales order line numbers by category, leveraging measures and DAX for accurate aggregation.

Explore division functions in Power Pivot (Excel) and SSAS Tabular DAX model by using mod to get remainders and quotient with a 5,2 example, and compare it to int's behavior.

Explore how abs and sign functions handle positive, negative, and zero values, and use sign with switch to classify product cost and sales amount comparisons.