Power Pivot With Dax (Basic To Advance) – English
Power Pivot With Dax (Basic To Advance) – English
Do any calculation now in Excel with powerpivot dax
Created by Ajay Parmar
Course Description
- What is a PowerPivot and how to install it.
- How to 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.
- 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
- Surprise Quiz for you to check your gained understanding.
- 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
Course Curriculum
Course Begins
-
Introduction to PowerPivot – Installation Process
10:44 -
Learn now how to import data into this incredible Tool
21:01 -
What if Data is outside the Excel – How to Import it then?
16:34 -
Create Calculated Columns and Measures – Its first step
48:21 -
DAX – Data analysis expressions – Basics of Incredible formula language
47:17 -
DAX ITERATORS – How they function and Rules to follow
39:15 -
DAX Filter – Adding more wings to Fly – Up, Up and Away
21:56 -
Introducing CALCULATED DAX – the most powerful and versatile function ever
18:28 -
What is this ALL Dax – Take your data control to next level
16:53 -
More Discussion on ALL Family Dax
35:30 -
ALL EXCEPT – You cannot miss this- Get ready to smile
10:45 -
ALL SELECTED DAX – One more powerful gift to Analysts by microsoft
15:39 -
Surprise Quiz for you – Let us check where do you stand?
42:09 -
One more exercise for you in DAX
12:39 -
Secret you never knew- Its fundamentals – Calculation Concept
16:23 -
How about Using VLOOKUP in PowerPivot – RELATED DAX -Simple yet Powerful
38:21 -
More discussion on RELATED Dax- Deep dive into it
14:52 -
What is a RELATEDTABLE and its practical Use in Data Analytics
16:40 -
More deep understanding – You will love it
06:26 -
Data Modelling with Relationships – One to One, Many to Many and One to Many
33:35 -
Data Modelling Many to Many Relationships Challenges
19:23 -
Many to Many Relationship – One more example
07:03 -
Surprise Quiz for you – Let us check where do you stand?
25:41 -
How to use IF Function as a DAX Measure and as a Calculated column
43:23 -
How to use IF DAX as a Measure – Just fundamentals and nothing more.
07:50 -
Switch DAX -Another alternative of IF Function – It is super fast
16:19 -
Introducing DAX Studio & TOPN Dax -Part1
30:03 -
How to use TOPN Dax – Part2
04:55 -
How to use TOPN Dax – Part3
02:47 -
HASONEVALUE DAX – It is outstanding Part1
13:13 -
HASONEVALUE DAX – It is outstanding Part2
14:30 -
SUMMARIZE – Long awaited DAX – Its simply awesome
39:07 -
SUMMARIZE DAX – Continued..
45:31 -
Learn VALUES DAX Part1
18:06 -
VALUES DAX Part2
15:43 -
IMPLICIT and EXPLICIT Measures – Must know thing
17:41 -
Calculate Yearly, Monthly and Quarterly Cumulative in Pivots. DATESMTD,QTD,YTD
26:53 -
Find FIRST Date and LAST Date based on filter Context.
07:24 -
Start of year and End of month explained
03:55 -
Find closing balance – Very useful for finance professionals
03:55 -
One of my favorites – DATE ADD
14:56 -
How to add or avg or do any calculation in pivots between specific dates
12:48
Power Pivot With Dax (Basic To Advance) – English
Course Description
- What is a PowerPivot and how to install it.
- How to 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.
- 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
- Surprise Quiz for you to check your gained understanding.
- 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
Course Curriculum
Course Begins
-
Introduction to PowerPivot – Installation Process
10:44 -
Learn now how to import data into this incredible Tool
21:01 -
What if Data is outside the Excel – How to Import it then?
16:34 -
Create Calculated Columns and Measures – Its first step
48:21 -
DAX – Data analysis expressions – Basics of Incredible formula language
47:17 -
DAX ITERATORS – How they function and Rules to follow
39:15 -
DAX Filter – Adding more wings to Fly – Up, Up and Away
21:56 -
Introducing CALCULATED DAX – the most powerful and versatile function ever
18:28 -
What is this ALL Dax – Take your data control to next level
16:53 -
More Discussion on ALL Family Dax
35:30 -
ALL EXCEPT – You cannot miss this- Get ready to smile
10:45 -
ALL SELECTED DAX – One more powerful gift to Analysts by microsoft
15:39 -
Surprise Quiz for you – Let us check where do you stand?
42:09 -
One more exercise for you in DAX
12:39 -
Secret you never knew- Its fundamentals – Calculation Concept
16:23 -
How about Using VLOOKUP in PowerPivot – RELATED DAX -Simple yet Powerful
38:21 -
More discussion on RELATED Dax- Deep dive into it
14:52 -
What is a RELATEDTABLE and its practical Use in Data Analytics
16:40 -
More deep understanding – You will love it
06:26 -
Data Modelling with Relationships – One to One, Many to Many and One to Many
33:35 -
Data Modelling Many to Many Relationships Challenges
19:23 -
Many to Many Relationship – One more example
07:03 -
Surprise Quiz for you – Let us check where do you stand?
25:41 -
How to use IF Function as a DAX Measure and as a Calculated column
43:23 -
How to use IF DAX as a Measure – Just fundamentals and nothing more.
07:50 -
Switch DAX -Another alternative of IF Function – It is super fast
16:19 -
Introducing DAX Studio & TOPN Dax -Part1
30:03 -
How to use TOPN Dax – Part2
04:55 -
How to use TOPN Dax – Part3
02:47 -
HASONEVALUE DAX – It is outstanding Part1
13:13 -
HASONEVALUE DAX – It is outstanding Part2
14:30 -
SUMMARIZE – Long awaited DAX – Its simply awesome
39:07 -
SUMMARIZE DAX – Continued..
45:31 -
Learn VALUES DAX Part1
18:06 -
VALUES DAX Part2
15:43 -
IMPLICIT and EXPLICIT Measures – Must know thing
17:41 -
Calculate Yearly, Monthly and Quarterly Cumulative in Pivots. DATESMTD,QTD,YTD
26:53 -
Find FIRST Date and LAST Date based on filter Context.
07:24 -
Start of year and End of month explained
03:55 -
Find closing balance – Very useful for finance professionals
03:55 -
One of my favorites – DATE ADD
14:56 -
How to add or avg or do any calculation in pivots between specific dates
12:48