Highlights
- Understanding Filter context
- Best Practice guidelines and development tips
- Commonly Used DAX Functions: SUM, AVERAGE, COUNTROWS, IF, BLANK
- Understand use of CALCULATE to modify filter context
- Time Intelligence prerequisites
- Create Dates table in DAX
- Build a Time Intelligence report with Month-on-Month change
- Create drillable Period-on-Period changes
- Use functions: SAMEPERIODLASTYEAR, PARALLEPERIOD, DATESYTD
- Implement Fiscal Years
- Reduce measure count using Calculation Groups
- Calculate percentage contributions with ALL, ALLEXCEPT, FILTER, REMOVEFILTERS
- Tips for understanding complex DAX code
- Moving Measures to a measure table
- Dynamic titles driven by DAX
- Key Performance Indicators
- Use of DAX Variables
- Create a polymorphic measure using SELECTEDVALUE and a slicer
- Understanding Row Context, Iteration, and Context transition
- Avoid creating DAX columns by using the X functions: e.g. SUMX
- Pareto classification (80:20 rule) using row context formulas
- DAX as a query language
- Learn DAX STUDIO’s powerful export features.
- Resolving performance problems
Course Details
DAX
- Examples with SUM, AVG, COUNT, DISTINCTCOUNT
- Common DAX patterns
- Scenarios for FILTER, SELECTEDVALUE
- Ranking functions, TOPN
- When to use the X-functions
- Create filtered measures with CALCULATE
- Virtual tables
- Percents of Total with ALL, ALLSELECTED, ALLEXCEPT
BEST PRACTICES
- Safe DIVIDE
- Housekeeping with measure groups
- Interim measures
- Measures for conditional formatting
- Use of DAX variables
- Using the BLANK() function
CONTEXT
- Be clear on Measures vs Columns
- Understand Filter Context
- Propagate filter through Context Transition
- Iterate with Row Context
- Implement ABC Classification
RELATIONSHIPS
- Filter flow direction through relationships
- RELATED vs LOOKUPVALUE
- Data tables vs lookup tables
- Accommodating different table granularities
- Data lineage
TIME INTELLIGENCE
- Benefits of Dates table
- Year-on-year comparisons
- SAMEPERIODLASTYEAR, PARALLELPERIOD
- Cumulative Total to Date calculations
- Fiscal years/quarters
- Special accounting periods
- Pro-rata view for incomplete trading periods
- Events in progress
- Budget vs Actual comparison methodology
PRODUCTIVITY MISC
- Understand IntelliSense syntax notation
- Dynamic titles, CONCATENATEX
- Introduction to DAX Studio
- DAX query examples
- Capture DAX query traffic
- Identify and fix performance issues
- Use Calculation Groups
- What-if analysis
- Design for budget and actual figures on same chart
Who should attend
- Business Analysts
- Financial Analysts
- Quants
- Data Scientists
- Staff already using Power BI or who have attended the introductory course and wish to explore its features in more depth
- Developers and Super Users/Power Users seeking to maximise their use of Power BI
Note: This course requires delegates to run Power BI Desktop. Mac users must make arrangements to use a PC.
Feedback
4.8 out of 5 average
"Great coverage of DAX principles including basic DAX formulae and time intelligence. The trainer was very knowledgeable and informative." AW, Senior BI developer, Power BI - DAX, March 2021