|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/powerpivot-with-dax-basic-to-advance-13-hours/
课程评论：没有评论
Coursera 课程《PowerPivot with DAX - Basic to Advance》学习总结 本课程时长13小时，旨在帮助学员掌握PowerPivot和DAX（Data Analysis Expressions）这一强大的数据分析工具。 **第一部分：PowerPivot与DAX基础** * **PowerPivot介绍**：讲解PowerPivot是什么，为什么需要它，以及它相比普通透视表（Pivot Tables）的优势。 * **数据导入**：学习如何从Excel、CSV、文本文件或其他数据库导入数据。 * **PowerPivot界面与基本操作**：熟悉PowerPivot的Ribbon菜单，掌握排序、筛选等基础功能。 * **度量值 (Measures) 与计算列 (Calculated Columns)**：了解它们的定义、编写位置以及与DAX的关系。 * **DAX基础**：介绍DAX作为一种公式语言，并详细讲解度量值与计算列的区别及选择依据。 * **基础DAX函数**：学习SUM、COUNT、COUNTA、COUNTBLANK等聚合函数，并澄清它们是度量值还是计算列的困惑。 * **迭代器函数 (Iterators)**：深入理解SUMX、COUNTX、AVERAGEX等迭代器函数的原理和应用。 * **CALCULATE 函数**：学习这一核心DAX函数，理解其“条件计算”的能力，并了解其别称“CALCULATE IF”的原因。 * **ALL 系列函数**：深入解析ALL、ALLSELECTED、ALLE XCEPT等函数，并通过实例说明它们在数据分析中控制数据范围的作用。 * **实际应用**：通过计算不同产品基于团队总数的百分比等实际案例巩固所学。 **第二部分：PowerPivot关系与高级DAX** * **关系 (Relationships) 创建**：详细讲解PowerPivot中一对一、多对多、一对多、多对一等关系类型，及其创建时的挑战和示例。 * **DAX函数深入**： * **HASONEVALUE**：结合IF语句，深入理解HASONEVALUE函数的用法。 * **VALUES函数**：学习VALUES函数的作用，及其如何通过提供更多控制来改变透视表计算。 * **隐式度量值与显式度量值**：理解两者的区别，以及优先创建显式度量值的原因。 * **TOPN函数**：学习TOPN函数的使用，以及在实际场景中的应用。 * **IF函数**：学习如何编写从简单到嵌套的IF函数，并掌握其作为计算列和度量值的不同用法及区别。 * **SWITCH函数**：学习SWITCH函数作为IF函数的替代方案。 * **SUMMARIZE函数**：学习SUMMARIZE函数，以及如何将其应用于总计和小计的计算。 * **自定义透视表计算**：学习如何自定义透视表的字段计算，以及总计和小计的显示逻辑。 * **数据条件显示**：掌握如何选择性地显示或隐藏特定字段的数据。 * **错误处理**：学习如何识别和纠正DAX计算中的错误信息。 **第三部分：时间智能与实践** * **时间智能函数 (Time Intelligence Functions)**： * 学习如何按年、月、季度计算累计销售额。 * 学习如何计算月度或年度的期末余额。 * 学习如何找到年初、年末、年末或季末的日期。 * **日历表 (Calendar Table)**： * 理解创建日历表的重要性，尤其是在使用时间智能函数之前。 * 学习如何编辑或创建日历表与主表的关联。 * **DATEBETWEEN函数**：学习如何基于两个日期进行各种计算。 * **DAX Studio**：介绍DAX Studio是什么，如何下载和使用它来优化透视表示计算，并探讨其使用原因和面临的挑战。 * **综合测试**：设置惊喜测验，以检验学员对所学知识的掌握程度。 本课程通过理论讲解、函数解析以及大量的实际操作示例，全面提升学员在PowerPivot和DAX方面的数据分析能力。
Section1: We are covering Power Pivot with DaxWhat is a PowerPivot and Why do we need it. How it is going to outshine our normal PivotsImport 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 featuresKnow 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, AVERAGEXWhy they are called ITERATORS - Its fundamentalsLearn 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 challengesDetailed discussion on HASONEVALUE Dax including IF statementsWhat 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 everythingHow 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 - SUMMARIZEHow 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 wiseHow to find the start of the year How to find the end of the year, month or quarterCreate 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 - DATEBETWEENSurprise Quiz for you to check your gained understanding.
