ADVANCED pOWER BI WITH
DAX Masterclass:
Gain Insights from Data with Effective DAX Formulas for Common and Advanced Business Scenarios
Download brochure
Click on the link below to request for Advanced Power BI with DAX Training Malaysia brochure
| 17-19 Jul | Kuala Lumpur |
| 17-19 Jul | Live Online Class |
| 11-13 Sep | Kuala Lumpur |
| 11-13 Sep | Live Online Class |
| 4-6 Dec | Kuala Lumpur |
| 4-6 Dec | Live Online Class |
Workshop Overview
You can easily create Power BI reports without using any DAX formulas at all. But, what if you need to analyse growth percentage across product categories and for different date ranges? Or, you need to calculate year-on-year growth compared to market trends? These are only possible with DAX formulas.
DAX is the native language of Power BI Desktop. Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information and insights you need, you can begin to solve real business problems that affect your bottom line.
The training is aimed at users of Power BI Desktop who want to learn and master the DAX language. You will gain solid knowledge in DAX - contexts, rules of evaluation and DAX functions. You will also learn the ‘ins and outs’ of writing efficient expressions, variables, time intelligence calculations, cumulative totals and dynamic titles, and how to use DAX Studio to debug and increase performance.
The training course will show you how to use Power BI’s functionality to create complex calculations that will allow you flexibility beyond that offered by the standard toolset. You will have the ability to write DAX formulas for common and advanced business scenarios.
Key Takeaways
- Understand the evaluation contexts and engine behind DAX
- Gain knowledge of commonly used DAX functions
- Create DAX expressions for calculated columns and measures
- Apply Sum, Average, Count and DistinctCount
- Learn to create a measure table
- Understand how to use Variables, Calculations and Cumulative Totals
- Acquire skill in Copying and Moving Measures
- Use Variables in Calculation
- Explore Different granularity: calculate allocated weight
- Make use of tools to compliment creating and managing DAX
- Use DAX as a query language in DAX Studio
- Capture and debug queries with DAX Studio
- Use DAX studio to increase perfor
- Evaluate Contexts and Relationships
- Understand the use of CALCULATE
- Learn Table Functions: ALL, ALLEXCEPT, FILTER, DISTINCT, VALUES
- Explore Advanced Evaluation Context DAX
- Roll totals for different periods
- Learn IF, HasOneValue
- Use a Safe Divide Function and the Blank() Function
- Create more complex expressions using methods to build and prove an outcome
- Use techniques to optimise an expression for better efficiency
Methodologies
Clear explanation of theories coupled with hands-on exercises for a firm grasp of DAX formulas through:
- Practical Examples
- Case Studies
- Short Exercises
- Group Discussions
PREREQUISITES
Attendees need to have a basic knowledge of data modelling.
Who Should Attend
This workshop is crucial for managers, business professionals, business owners, executives and anyone who uses Power BI. This training is highly recommended for:
- General Managers
- Finance Managers
- Marketing Managers
- Sales Managers
- HR Managers
- Project Managers
- Business Analysts
- Financial Analysts
- System Analysts
- Data Scientists
- Developers
- Power BI Super Users
- Power BI Power Users
- Anyone looking to get more out of Power BI or Power Pivot for Excel
Software Used
The course is demonstrated
using Power BI Desktop
Programme Agenda
Session 1: DAX
- Commonly Used DAX Functions
- DAX contexts
- DAX efficiency
- DAX references
- DAX best practices
Session 2: DAX best practices
- 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
Session 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
Session 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
Session 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
Session 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
Session 7: ITERATORS
- Working with iterators
- MINX and MAXX
- Useful iterators
- RANKX
- ISINSCOPE
Session 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
Session 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
Session 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
Session 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
- The TREATAS function
- A few ORs or TREATAS
- Using TREATAS and KEEPFILTERS
- Using TREATAS for virtual relationships
Session 12: DAX STUDIO
- Dax as a query language
- Capturing and debugging queries with Dax Studio
- Power BI referencing
Session 13: HIERARCHIES IN DAX
- What are hierarchies
- FILTER and CROSSFILTER
- Percentages over hierarchies
- Parent-child hierarchies
Session 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