Power Pivot With Dax (Basic To Advance) – English
Do any calculation now in Excel with powerpivot dax
Power Pivot,Office Productivity,Microsoft
Lectures -41
Duration -14 hours
30-days Money-Back Guarantee
Get your team access to 10000+ top Tutorials Point courses anytime, anywhere.
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
Goals
What will you learn in this course:
This is PowerPivot with DAX Part2 and it is in continuation to part1. This course will make you superb in PowerPivot DAX Lanaguage.
This course will give you insight on the importance of DAX in PowerPivot and how it has no comparison with old traditional pivots.
You will learn HASONEVALUE Dax
You will learn IF statements - Basic to advance
How to use SWITCH Dax - Which one is better - IF or SWITCH
How to use IF as a calculated column and as a measure and which option is best or should you use when.
TOPN Dax for calculating the TOP entries from different date ranges
How to customize the pivot fields calculations with grand totals and sub totals
In section2 learn about Time Intelligence DAX functions from scratch.
Practical examples in day to day life also has been explained .
Learn what is DAX Studio
How to download it and how to launch it in power pivot
Do we really need DAX Studio and for what purposes it can be used.
Another amazing DAX - Summarize with practical examples.
What is implicit and Explict measures
Which one better and why - Implicit or Explicit
Prerequisites
What are the prerequisites for this course?
You should have seen this course Part1 as this is in continuation to the previous one.
You should be familiar with PowerPivot DAX language as this course is not intended for those who lacks knowledge of power pivot.
You should have excel 2013 or afterwards versions which supports powerpivot.
Curriculum
Check out the detailed breakdown of what’s inside the course
Course Begins
41 Lectures
- Introduction to PowerPivot – Installation Process 10:43 10:43
- Learn now how to import data into this incredible Tool 21:00 21:00
- What if Data is outside the Excel – How to Import it then? 16:33 16:33
- Create Calculated Columns and Measures – Its first step 48:20 48:20
- DAX – Data analysis expressions – Basics of Incredible formula language 47:16 47:16
- DAX ITERATORS – How they function and Rules to follow 39:15 39:15
- Introducing CALCULATED DAX – the most powerful and versatile function ever 18:27 18:27
- What is this ALL Dax – Take your data control to next level 16:52 16:52
- More Discussion on ALL Family Dax 35:30 35:30
- ALL EXCEPT – You cannot miss this- Get ready to smile 10:44 10:44
- ALL SELECTED DAX – One more powerful gift to Analysts by microsoft 15:38 15:38
- Surprise Quiz for you – Let us check where do you stand? 42:08 42:08
- One more exercise for you in DAX 12:38 12:38
- Secret you never knew- Its fundamentals – Calculation Concept 16:23 16:23
- How about Using VLOOKUP in PowerPivot – RELATED DAX -Simple yet Powerful 38:20 38:20
- More discussion on RELATED Dax- Deep dive into it 14:51 14:51
- What is a RELATEDTABLE and its practical Use in Data Analytics 16:39 16:39
- More deep understanding – You will love it 06:26 06:26
- Data Modelling with Relationships – One to One, Many to Many and One to Many 33:34 33:34
- Data Modelling Many to Many Relationships Challenges 19:23 19:23
- Many to Many Relationship – One more example 07:02 07:02
- Surprise Quiz for you – Let us check where do you stand ? 25:40 25:40
- How to use IF Function as a DAX Measure and as a Calculated column 43:23 43:23
- How to use IF DAX as a Measure – Just fundamentals and nothing more. 07:49 07:49
- Switch DAX -Another alternative of IF Function – It is super fast 16:18 16:18
- Introducing DAX Studio & TOPN Dax -Part1 30:03 30:03
- How to use TOPN Dax – Part2 04:55 04:55
- How to use TOPN Dax – Part3 02:46 02:46
- HASONEVALUE DAX – It is outstanding Part1 13:12 13:12
- HASONEVALUE DAX – It is outstanding Part2 14:29 14:29
- SUMMARIZE – Long awaited DAX – Its simply awesome 39:07 39:07
- SUMMARIZE DAX – Continued.. 45:30 45:30
- Learn VALUES DAX Part1 18:05 18:05
- VALUES DAX Part2 15:43 15:43
- IMPLICIT and EXPLICIT Measures – Must know thing 17:41 17:41
- Calculate Yearly, Monthly and Quarterly Cumulative in Pivots. DATESMTD,QTD,YTD 26:53 26:53
- Find FIRST Date and LAST Date based on filter Context. 07:24 07:24
- Start of year and End of month explained 03:54 03:54
- Find closing balance – Very useful for finance professionals 06:42 06:42
- One of my favorites – DATE ADD 14:56 14:56
- How to add or avg or do any calculation in pivots between specific dates 12:48 12:48
Instructor Details
ajay parmar
eCourse Certificate
Use your certificate to make a career change or to advance in your current career.
Our students work
with the Best
Related Video Courses
View MoreAnnual Membership
Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video Courses
Subscribe nowOnline Certifications
Master prominent technologies at full length and become a valued certified professional.
Explore Now