fbpx

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 brochure

17-19 Jul Live Online Class
17-19 Jul Kuala Lumpur
11-13 Sep Live Online Class
11-13 Sep Kuala Lumpur
4-6 Dec Live Online Class
4-6 Dec Kuala Lumpur
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
    POPULAR TRAINING COURSES

    Power BI Course
    Power BI DAX Course
    Excel Dashboards Course
    Excel Infographics Course
    Excel Power Query Advanced Course
    PowerPoint Infographics Advanced Course