{Big} Data Analytics with Advanced Power BI DAX:.
Gain insights with effective DAX formulas for common and advanced business scenarios.
Course overview.
POWER BI DAX TRAININGData is everywhere. Get the most out of your data by creating effective DAX formulas. When you get the information and insights you need, you can begin to solve real business problems that affect your bottom line.
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. The training is aimed at Power BI users 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.
POWER BI DAX TRAINING- Perform advanced analytics and gain insights from your data with DAX
- Perform what-if scenario analysis to analyse different scenarios and the consequences that may occur as conditions change
- Learn how to develop a Power BI data model for advanced scenario analysis
- Gain knowledge of commonly used DAX functions
- Understand the evaluation contexts and engine behind DAX
- Understand Power BI model relationships
- Understand the use of star schema design principles in data modelling
- Create DAX expressions for calculated columns and measures
- Use variables to improve performance, reliability, readability and reduce complexity
- Understand the use of CALCULATE
- Learn to add, modify and remove filters with ALL, ALLEXCEPT, ALLSELECTED and FILTER
- Learn to build a date table with DAX
- Perform complex time-based reporting with time intelligence functions
- Create more complex expressions using methods to build and prove an outcome
- Use techniques to optimise an expression for better efficiency
- Capture and debug queries with DAX Studio
- Use DAX studio to increase performance
Who should attend.
This workshop is crucial for managers, business professionals, business owners, executives and anyone who uses DAX to analyse big data. 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
Prerequisites.
Attendees need to have basic knowledge of Power BI
The programme.
POWER BI DAX TRAININGSESSION 1Data & DAX
- Overview of training aim, objectives and agenda
- Using DAX for advanced data analytics
SESSION 2DAX (data Analysis Expressions) Basics
- Using DAX to perform advanced analysis to gain insights
- Understanding DAX - tables/columns (DAX) vs cells (Excel)
- Understanding the difference between:
- Implicit measure
- Explicit measure
- Understanding the difference between:
- Calculated column
- Measure
- Power Query vs DAX: How do you decide?
SESSION 3DAX Context
- Understanding context as the key to:
- Building high-performing, dynamic analyses
- Troubleshooting problems in formulas
- Types of evaluation context:
- Filter context
- Row context
- Types of filters:
- Implicit filter (from the current query)
- Explicit filter (external filters outside the visuals)
- Context transition in calculated columns
SESSION 4DAX Calculation Engines - The Aggregators And Iterators
- How DAX aggregators and iterators work differently
- Aggregating functions:
- SUM, AVERAGE, MIN, MAX
- COUNT/COUNTA/COUNTBLANK/COUNTROWS
- Iterating functions:
- SUMX, AVERAGEX, MINX, MAXX
SESSION 5Commonly Used DAX Functions
- Filter functions: CALCULATE, FILTER
- CALCULATE: The most used function in DAX
- Using CALCULATE to add, modify or remove filters
- Contex transition with CALCULATE
- Using FILTER to reduce the number of rows in the table
- Safe DIVIDE function
- Logical functions:
- IF
- SWITCH (Nested IFs)
- AND
- OR
- Logical operators: AND (&&) and OR (II)
SESSION 6DAX Tips And Tricks
- Simple to advanced: Start with simple formulas, then branch out to the advanced calculations
- Variable: Using variables in DAX formulas to help write complex and efficient calculations
SESSION 7Data Modelling
- Creating and editing relationships
- Active vs inactive relationships
- Fact table vs dimension table
- Relationship cardinality
- One-to-one
- One-to-many
- Many-to-many
SESSION 8Table Manipulation Functions
- Using table manipulation functions to return a table or manipulate existing tables
- Using VALUES and DISTINCT to extract distinct values from a column or table
- Difference between VALUES and DISTINCT
- Using VALUES and iterating functions to group and analyse data in multiple different categories
SESSION 9More Filter Functions
- ALL: Used to ignore filters applied to a table or column(s)
- ALLEXCEPT: Using ALLEXCEPT to remove filters on many, but not all, columns in a table
- ALLSELECTED: Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters
- Using ALLSELECTED to calculate running or cumulative total
SESSION 10DAX Information Functions
- DAX information functions provide information about data type or filter context
- Data type:
- ISBLANK
- ISERROR
- ISLOGICAL, ISTEXT, ISNUMBER
- ISEVEN, ISODD
- Filter context:
- ISFILTERED: Returns true when there are direct filters on the specified column
- ISCROSSFILTERED: Returns true when the specified table or column is crossfiltered
SESSION 11Building A Date Table
- Auto date/time limitations
- Calendar periods only
- Customisation is not possible
- Single table date filtering
- DAX date functions
- CALENDAR
- CALENDARAUTO
- Build date tables for:
- Calendar year, which ends in December
- Fiscal year, which does not end in December
- Expand date table with ADDCOLUMNS, YEAR, MONTH, WEEKNUM, WEEKDAY
- Using FORMAT to create custom date formats
- Creating a Date Hierarchy
SESSION 12Time Intelligence Functions
- Time intelligence functions support calculations to compare and aggregate data over time periods - days, months, quarters and years
- Computing running totals with:
- DATESYTD, DATESQTD, DATESMTD
- MAX and ALLSELECTED
- DATEADD: The best and most versatile time intelligence function to compare time periods
- Obtain opening or closing balances with:
- OPENINGBALANCEMONTH, OPENINGBALANCEYEAR
- CLOSINGBALANCEMONTH, CLOSINGBALANCEYEAR
- STARTOFMONTH, STARTOFYEAR
- ENDOFMONTH, ENDOFYEAR
- Calculating rolling balances with DATESINPERIOD
- Calculating moving averages with AVERAGEX
- Using DATEDIFF to calculate time intervals between two input dates
SESSION 13Relationship Functions
- RELATED function follows an existing many-to-one relationship to fetch the value from the specified column in the related table
- USERELATIONSHIP specifies the relationship to be used in the calculation by the end point columns of two related tables
- Manage inactive relationships with USERELATIONSHIP in Power BI
- How to deal with multiple date columns
SESSION 14Selectedvalue & Hasonevalue
- SELECTEDVALUE returns the value when there's only one value in the specified column, otherwise returns the alternate result.
- Using SELECTEDVALUE instead of HASONEVALUE, IF and VALUES to test whether a column is filtered by a specific value
- Use SELECTEDVALUE to harvest slicer selction
SESSION 15DAX Studio
- Third-party tool to measure and improve the speed of DAX queries.
- Capturing and debugging queries with DAX Studio
SESSION 16Additional Uses Of DAX
- Creating dynamic titles with DAX
- Creating conditional formatting with DAX
- Using row level security to restrict data access
- Creating and testing roles
- Security - adding users to defined roles
SESSION 17What-if Analysis With DAX
- Types of what-if analysis:
- Sensitivity analysis
- Scenario analysis
- Create modeling parameters
- How to create multi-layered scenario analysis
- Advanced scenario analysis techniques with DAX
- How to develop the right data model for advanced scenario analysis
- Experiment with different slicer combinations to explore various scenarios and analyse the impacts of different filters on your data
SESSION 18Hands-on What-if Analysis Project
- Applying Power BI DAX skills
- Choice of scenarios:
- Pricing strategy & sales forecasting
- Demand planning & inventory management
- Production planning
- Bring your own scenarios
- Throughout the workshop, participants will work through practical exercises to enhance their Power BI DAX knowledge to analyse and gain insights from data
Your trainer.
POWER BI DAX TRAININGAlvin Yong is a UK-registered Fellow Chartered and Certified Accountant (FCCA). He obtained his Master of Science in Professional Accountancy (Merit) in 2017 from the University of London.
To bring.
POWER BI DAX TRAININGReviews from our clients.
POWER BI DAX TRAINING“Content is very good, notes are sufficiently detailed, presentation is clear and pace is good. Alvin entertained questions calmly, clearly, and with sufficient depth. Very knowledgeable and qualified for the training subject.”
“Alvin is an excellent trainer. He led interesting, interactive and focused discussions during the workshop.”
“Alvin was well prepared, knowledgeable and easy to follow. Level of detail was where it needed to be and the documentation was very thorough. The course went quickly which was always a good sign.”
“Fantastic trainer in terms of knowledge and skills. Extremely friendly, helpful and patient.”
“Good understanding and knowledge of the subject. Attentive and responsive to participants' queries. Presented the subject very clearly.”
“The trainer is knowledgeable. He was helpful. He handled the class material well.”
“Very informative. Gave an excellent count of how-to's.”
Related courses.
POWER BI · DAXReady when your team is.
Public runs across the region and live online — or we scope an internal cohort around your actual reports.
Enquire about in-house training