Course NT-PowerP.DAX Using of Power Pivot and DAX in Excel as Data Analysis Tools
Nowadays, the volume and complexity of the data that almost every company works with is growing rapidly. In this regard, the question of a business analysis tool (Business Intelligence, BI) is more acute than ever.
Microsoft Excel is one such tool. Not the only one, but certainly the most popular. We can confidently say that every analyst is familiar with his pivot tables. However, "classic" pivot tables have certain limitations, which sooner or later become a critical task to overcome. The Power Pivot add-in and the DAX language are designed to solve this problem. This training is dedicated to their study.
After completing this course, students will be able to:
- download data from different sources (text files, Excel workbooks, databases, etc.) and link them together, forming the so-called data model.
- extend the data model by creating DAX calculated fields and measures that allow you to perform calculations that are not possible in "regular pivot tables" or using regular Excel formulas.
- build pivot tables and charts using a data model as a data source, i.e. operating not one, but several related tables.
Audience Profile
Analysts, managers and database administrators, novice programmers and professionals working with Microsoft Excel.
Before attending this course, students must have:
- experience in Windows and MS Excel
- good skills in Excel (sorting and filtering data, subtotals, VLOOKUP, smart tables, pivot tables and charts)
- experience in Access preferred
- Working with the data model through the Power add-in Pivot
- Power add -in Pivot, its purpose and "habitat"
- The concept of a data model, its structure, the principles of its construction
- Extracting data from various external sources (Access database, CSV file, Excel spreadsheet, etc.)
- Relationships between tables. Link types. Star and snowflake data model schemas Reference tables (dimensions) and fact tables
- Installing and configuring relationships between tables. Active and inactive links Direction of cross-filtering
- Creating and using date tables
- Creating Hierarchies
- KPI setting
- Custom sort
- DAX language and its use in Power Pivot
- The concept of the DAX language, its purpose and "habitat"
- Calculated columns, tables, measures (calculated fields)
- The concept of context. Row context and filter context
- Create calculated fields. Some functions for processing texts and numbers.
- Creation of simple measures. SUM, AVERAGE, MIN, MAX, DISTINCTCOUNT, COUNTA, COUNTROWS functions
- RELATED, IF, DIVIDE, SWITCH, BLANK functions
- Functions CALCULATE, ALL, ALLEXCEPT, ALLSELECTED, HASONEVALUE, FILTER
- VALUES, DISTINCT functions
- Using iterator functions SUMX, AVERAGEX, MAXX, MINX, RANKX, TOPN
- Working with dates, to create and customize calendars. Calculation funded results. Functions CALENDAR, CALENDARAUTO, NOW, DATE, FORMAT, YEAR, MONTH, DAY, DATESYTD, TOTALYTD, DATESQTD, DATESMTD, ENDOFMONTH, CLOSINGBALANCEMONTH, LASTNONBLANK, DATEADD, PARALLELPERIOD, PREVIOUSMONTH, DATESBETWEEN, FIRSTDATE, LASTDATE
- Using unrelated tables
- Relationship Management with USERELATIONSHIP and CALCULATE Functions
- Using Variables
- Visualize data model data with pivot tables and charts
Sign up for the closest date
Copyright © 2024 Networking Technologies