PowerPivot with DAX - Basic to Advance (13 Hours)

Discover how Power Pivot differs from traditional pivot and why data analysts should switch, and learn how to install and enable Power Pivot in various Excel versions.

Learn how to import Excel and external data into PowerPivot, create named tables, build the data model, and begin using DAX to create calculations and drive dashboards.

Learn how to import external data into PowerPivot from text, Excel, Access, SQL Server, and other sources, then create relationships and build pivot tables for analysis.

Learn how Power Pivot uses DAX to create calculated columns and measures, understand row-by-row vs filter context, and decide when to use each for pivot analyses.

Explore DAX basics in PowerPivot by creating measures, not calculated columns. Learn key functions like count, distinct count, counta, count rows, divide, and how filter context shapes results.

explore iterator functions such as sumx and countx in power pivot, learn row-by-row calculations with measures, and compare them with calculated columns, filters, and scalar values.

Learn how the CALCULATE function in DAX uses expression and filter parameters to sum, count, and average with criteria, using filters and existing measures.

explains the all, all selected, and all set DAX functions to ignore filter context, compute total revenue, and show each item’s contribution as a percentage in PowerPivot.

Learn to use the all function to ignore book name filters while other filters apply, computing total revenue in a pivot with slicers.

Master the all except dax function to selectively ignore filter context while preserving a specific zone filter, using pivots and total revenue calculations.

Master all selected in DAX to compute author contributions across years using slicers and the calculate function, revealing year-specific percentages of total revenue.

Master PowerPivot with DAX from basics to advanced through hands-on exercises, creating zone revenue measures, year filters, category contributions, and author monthly revenue.

Demonstrate building DAX measures in PowerPivot to compute total revenue from quantity and price, filter by months, authors, and year, and assess author contributions for Jan–Feb and May–Jun.

Explore how PowerPivot measures in DAX compute the grand total independently of subtotals, using filter context and the all family, with distinct count and total quantity across zone pivots.

Explore the related DAX function in Power Pivot, a Vlookup-like lookup across related tables, and how one-to-many relationships enable dynamic calculated columns and advanced pivot analysis.

Explore how to handle many-to-many in PowerPivot, why it’s problematic, and convert to a one-to-many model using a unique item list and related tables to compute customer revenue.

Resolve many-to-many data issues in PowerPivot by creating a one-to-many relationship between unique customers and their visits and purchases, then analyze revenue and visits with a pivot table.

Learn to transform wishlist data with Power Query, load it into Power Pivot, and create DAX measures to identify arrived books for each customer's wishlist for outreach.

Master the if function in PowerPivot with DAX, applying it to calculated columns and measures with nested logic, true/false outcomes, and Excel-style guidance.

Explore using the if function as a measure in PowerPivot, addressing aggregation and filter context with sum of rent paid to classify revenue as good or poor.

Learn how to use switch in DAX to handle multiple conditions, compare values with true, and choose switch over if, with examples mapping months to quarters and status outcomes.

Learn to use the Topn Dax function to return the top rows and sum the top three rent values, noting ties and scalar versus table outputs.

Explore how to identify top three revenue books using DAX and Power Pivot, filter pivots, sort data, and validate month-by-month performance in the data model.

Learn how to sort months in a pivot by month order, using the all days shortcut and the more options dialog to set ascending or descending order.

Learn how has one value DAX checks for a single value within a filter and uses true or false to control grand totals and subtotals in PowerPivot.

Explore how hasonevalue controls subtotals in PowerPivot with DAX, by adjusting filter context on book name versus month to customize subtotals and grand totals.

Explore the summarize DAX function to group data and control subtotals and grand totals. Use the top end and sumx to derive outputs from a virtual table with DAX Studio.

Learn to compute top three quantities per month and book using dax. Build a two-field summarize table, apply topN and sumx for accurate subtotals and grand totals.

Learn how values in dax remove duplicates to yield the unique count in a cost purchase table, and apply the naked columns rule to exclude Jan and April from totals.

Use values and if in PowerPivot to switch between total rent paid and total quantity for offers, and calculate average quantity for Jan and Apr.

Explain the difference between implicit and explicit measures in PowerPivot and Power BI, showing how explicit measures offer greater control and reusability across related tables using DAX.

Learn to build a calendar table and implement time intelligence in PowerPivot with DAX, calculating month till date, quarter till date, and year till date using date relations and measures.

Learn to compute first date and last date with time intelligence in PowerPivot, format dates, and view results by week, month, quarter, and year in pivots.

Master end-of-month and start-of-year DAX measures using calendar date, apply formatting, and explore how filter context shapes quarterly and monthly results in PowerPivot.

Master month, quarter, and year closing balances in PowerPivot with DAX by applying closing balance and total sales measures to calendar dates.

Learn to use the date add function to move dates forward or backward by year, month, or quarter, enabling year-on-year and quarter-to-quarter analysis in pivots.