PowerPivot with DAX - Basic to Advance (13 Hours)

Name: PowerPivot with DAX - Basic to Advance (13 Hours)
Rating: 4.8 (12 reviews)

Complete Data Analysis and making pivot reports

Created byajay parmar

Last updated 8/2024

English

What you'll learn

Students will learn PowerPivot and the role of DAX in it.
You will learn why PowerPivot is important for your career and how it has an edge over normal excel pivot.
How to create Reports using PowerPivot.
Implicit and Explicit Dax - Pls check curriculum for complete DAX details. We are discussing everything here

Course content

1 section • 37 lectures • 13h 1m total length

Get PowerPivot ready10:14
How to bring data into PowerPivot and other basic things about its window.19:34
Import external data in PowerPivot16:33
Create Calculated Column and Measures48:20
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.
DAX introduction47:16
ITERATOR Functions SUMX COUNTX etc39:15
CALCULATE Dax18:27
ALL DAX16:52
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.
ALL Dax Continues35:30
ALL Except DAX10:44
ALL Selected DAX15:38
QUIZ 142:08
QUIZ 212:38
How Measures work in PowerPivot16:23
Related DAX38:20
Many to Many RelationShip begins19:23
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.
Many to Many RelationShip7:02
Project25:40
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.
Use IF fx - Calculated Column and Measure43:23
More discussion on IF DAX7:49
SWITCH Dax16:19
TOPN Dax - Very important30:03
Question for you - Books revenue4:55
How to sort months in a pivot2:46
HASONEVALUE Dax13:12
HASONEVALUE with Subtotal14:29
Explore how hasonevalue controls subtotals in PowerPivot with DAX, by adjusting filter context on book name versus month to customize subtotals and grand totals.
Summarize Dax - My favorite - So useful39:07
Summarize dax - PowerPivot Subtotal Issue45:30
VALUES Dax18:05
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.
VALUES Dax Continues15:43
Implicit and Explicit17:41
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.
Date & Time functions26:53
FirstDate & LastDate7:24
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.
Start of an Year & End of Month3:54
Closing Balance6:42
Date Add14:56
Date Between12:48

Requirements

Knowledge of Excel formulas is an added advantage.
If you know the Basic pivots it is going to help you in PowerPivot chapters.
Any excel version like 2010 or above is fine.

Description

Section1: We are covering Power Pivot with Dax

- What is a PowerPivot and Why do we need it. How it is going to outshine our normal Pivots
- Import data from different sources like Excel, CSV Files or Text files or any other database.
- Get familiar with PowerPivot ribbon and sorting ,filtering options- all basic features
- Know the place to write Measures and Calculated Columns.
- What is DAX - A formula language. It is in very detail.
- Difference between Measures and Calculated Columns - Which one better and which should you choose?
- Basics DAX Functions - SUM,COUNT,COUNTA,COUNTBLANK, - Why they called Aggregate Functions .
- Are they DAX Measure or Calculate columns - All Confusion is cleared.
- Different Types of DAX Functions - ITERATORS - SUMX , COUNTX, AVERAGEX
- Why they are called ITERATORS - Its fundamentals
- Learn the most used and Powerful DAX - CALCULATE - It is also famous as CALCULATE IF, Know why?
- Deep Dive into ALL FAMILY FUNCTIONS - ALL / ALL SELECTED/ ALL EXCEPT with examples
- What is the role of ALL DAX in your Data analysis - Control the data the way you want.
- Practical Time - How to calculate overall percentage of different products based on the Team Total?
- What are RELATIONSHIPS in PowerPivot - One to One, Many to Many , One to Many and Many to one with examples and challenges
- Detailed discussion on HASONEVALUE Dax including IF statements
- What is a VALUES Function and how it can change the pivot calculations by giving you more control.
- What are implicit and explicit measures and why we should create latter ones.
- How to write TOPN Dax, one of my favorites.
- How to Use TOPN in practical scenarios .
- What is a DAX Studio and how do we download it and use it in Pivots - Challenges and reasons behind using dax studio.
- Learn the use of IF dax in PowerPivot. From writing simple IF functions to writing IF inside IFs , we are learning everything
- How to use IF as a calculated column and as a measure. What is the difference between both approaches.
- What is a SWITCH Dax and how it is used as an alternate of IF Dax.
- Learn one of the best dax - SUMMARIZE
- How to use summarize in your grand totals and sub-totals.
- Learn how to customize calculations for pivot fields and pivot grand totals.
- Learn how to avoid showing data for few fields and how to show data for other fields.
- Focus on error messages when they come during calculations . Learn what message says about DAX and correct the formulas accordingly.
- Time intelligence functions - How to find cumulative sums of sales year wise, or month wise or quarterly basis.
- How to calculate the closing balance - month wise or year wise
- How to find the start of the year
- How to find the end of the year, month or quarter
- Create calendar table and why it is important before using time intelligence functions and how to edit or create a relationship with main tables.
- how to add or whatever calculation to be done based on two dates - DATEBETWEEN
- Surprise Quiz for you to check your gained understanding.

Who this course is for:

This course is for Data , Research analysts and MIS people who work day and night on data and create reports
Those who want to add extra wings to their excel normal pivots - Definitely this course is for you
PowerPivot and DAX are the skills highly in demand in our industry. Booster for your career.