HomeTraining
Advanced DAX Training Pune, Bangalore India

Advanced DAX Training

ABOUT Data Analysis Expressions(DAX)
DAX stands for Data Analysis Expressions, it is language developed by Microsoft to interact with data in a variety of their platforms like Power BI, PowerPivot and SSAS tabular models. It is designed to be simple and easy to learn while exposing the power and flexibility of tabular models. In a way, you could compare it with Excel formulas on steroids. Using DAX will truly unleash the capabilities of Power BI. .

MODULE 1:
  • Data Modelling, Creating Relationships between data, and looking at Business Problem’s
  • Working with 1:1, 1:M, M:M tables
  • Understanding the Relationship Flow
MODULE 2 & 3:
  • Data Modelling, Creating Relationships between data, & looking at Business Problem’s Data Modelling, Creating
  • Introduction to DAX
  • Learning Data Analyze Expressions (DAX)
  • Functional Language Calculated
  • Columns and Calculated Fields DAX
  • Error Handling Overview of DAX
  • Functions RELATED
  • RELATEDTABLE functions FILTER
  • ALL functions CALCULATE function
  • Visual Totals and ALLSELECTED
MODULE 4:
  • What is Time Intelligence in DAX
  • Calendar Table Counting Working
  • Days Multiple Calendar Tables DAX
  • Filter Functions : CALCULATED
  • Performing arithmetic & aggregation
  • Operations on large data Writing
  • Statistical and logical DAX patterns
  • DAX creative formulas : SUMX() and Other functions
MODULE 5:
  • Business Scenario 1 - Contribution Analysis – How to find % of Totals
  • Business Scenario 2 - Actual v/s Budget Analysis – How to do allocations using DAX and work with 2 different granularity tables. Comparing Difference between years / products / subcategories
  • Business Scenario 3 - Calculate Top Performing Sales – Perform a range of analysis to find best selling Products during a specific period of time, for a specific period of time
  • Business Scenario 4 - Calculate Bottom Performing Sales – Perform a range of analysis to find least selling Products during a specific period of time, for a specific period of time
  • Business Scenario 5 - Working with Time Intelligence Functions such as YTD / MTD / QTD / Running Total's.
Module :6
  • Business Scenario 5 - Working with Time Intelligence Functions such as YTD / MTD / QTD / Running Total's
Topic 1:
  • Commonly Used DAX Functions
  • DAX contexts
  • DAX efficiency
  • DAX references
  • DAX best practices
Topic 2: DAX CONTEXTS
  • Filter context
  • The Row Context
  • The Column Context
  • Measures and Calculated Columns
  • Filter and Row Context in a measure
  • CALCULATE function
  • Context transition
  • Filter expressions of CALCULATE
  • Rules of evaluation
  • CALCULATE and FILTER
Topic 3: CONTEXTS IN CALCULATED COLUMNS
  • Row context in Calculated Columns
  • Filter context in Calculated Columns
  • Relationships in Row contexts
  • RELATED function
  • RELATEDTABLE function
  • Using RELATEDTABLE in a column
  • CALCULATED in row contexts
  • Row to filter context
  • Transformation
  • Duplicates in the transformation
  • LOOKUPVALUE function
Topic 4: ROW CONTEXTS IN MEASURES
  • Row contexts in measures
  • Simple iterator in a measure
  • Row context and a related table
  • Reuse measures
  • Implicit CALCULATE in a measure
  • Alternative approach using variable
Topic 5: TIME INTELLIGENCE
  • Time Intelligence prerequisites
  • Time intelligence functions MTD, QTD, YTD, SamePeriodLastYear
  • Date (Dimension) tables
  • Using in-built TOTALYTD
  • Date modifier functions
  • DATESBETWEEN, DATEADD and DATESINPERIOD
  • Using date modifier DATESYTD
  • The magic behind date table functions
  • YTD, MTD with CALCULATE
  • Rolling totals for different periods
  • IF, HasOneValue
  • Using a Safe Divide Function
  • Using the Blank() Function
  • Calculations over weeks
  • MAX or LASTDATE
  • Build an Example Time Intelligence Report
Topic 6: FILTER
  • Understanding the FILTER function
  • Contexts in the FILTER function
  • The filter context in functions
  • Simple filter expression
  • Comparing a value in the filter context
  • Adding another filter condition
  • The value of VALUES
Topic 7: ITERATORS
  • Working with iterators
  • MINX and MAXX
  • Useful iterators
  • RANKX
  • ISINSCOPE
Topic 8: TABLE FUNCTIONS
  • Summarising table
  • Using SUMMARIZE
  • Using ADDCOLUMNS and SUMMARIZE
  • Using SUMMARIZECOLUMNS as a better alternative
  • A summary table in a measure
  • Filtering a table
  • Ignoring filters
  • Mixing filters
  • Distinct
  • How many values for a column
  • ALLSELECTED
  • Tables and relationships
  • Table variables
Topic 9: TABLE JOINS
  • Introduction to Table Joins
  • The Inner Join
  • A join on unrelated tables - TREATAS
  • The Outer Join
  • The Cross Join
  • Using GENERATE for Joins
Topic 10: RANKING
  • Arguments in RANKX
  • Create a calculated column for ranking
  • Sort order of ranking
  • Definitions when ranking ties
  • Breaking Ties Manually
  • Creating a measure for ranking
  • Adjustments to make the RANKX measure work
Topic 11: PRACTICAL DAX
  • Introduction to DAX calculations
  • Comparing functions – min/max and time intelligence
  • Debugging with DAX
  • Alternative patterns
  • The Quick Measure version
  • The KEEPFILTERS function
Topic 12: DAX STUDIO XTS
  • Dax as a query language
  • Capturing and debugging queries with Dax Studio
  • Power BI referencing
Topic 13: HIERARCHIES IN DAX
  • What are hierarchies
  • FILTER and CROSSFILTER
  • Percentages over hierarchies
  • Parent-child hierarchies
Topic 14: MANY-TO-MANY RELATIONSHIPS
  • Many-to-many relationships
  • How to handle many-to-many relationships
  • Bidirectional filtering
  • Expanded table filtering
  • Comparison of the different techniques