COURSES · DATA ANALYTICS & BI · MALAYSIA

Data Analytics & Visualisation Series with Power BI.

Course enquiriesEnquire about this course →Next runSee the full schedule →New dates are announced there first.
Funding — MalaysiaAsk us about HRD Corp claim eligibility.

Course overview.

POWER BI AND DAX SERIES TRAINING MALAYSIA

Build a foundation in Power BI data visualisation and Power Query, then extend it with DAX for advanced business scenarios. This five-day programme progresses from interactive reports and data transformation through data modelling, evaluation contexts, time intelligence, debugging and what-if analysis.

The programme combines Power BI’s three-day syllabus with the final two days of the DAX masterclass. The shared DAX foundation is taught once. Public and in-house enquiries use this same programme content.

Key takeaways.

POWER BI AND DAX SERIES TRAINING MALAYSIA

Who should attend.

Analysts, managers, reporting professionals, executives and business owners who need to derive and share business insights, including finance, marketing, sales, HR and project managers; business, financial and system analysts; BI professionals, data scientists, developers and Power BI users.

Prerequisites.

No prior knowledge of programming or data analysis is required.

The programme.

POWER BI AND DAX SERIES TRAINING MALAYSIA
SESSION 1Data & AI
  • Overview of training aim, objectives and agenda
  • Using Power BI for business intelligence with self-service artificial intelligence (AI) capabilities
SESSION 2What Is Power BI?
  • Features comparison between Power BI components:
  • Power BI desktop
  • Power BI service (PowerBI.com)
  • Power BI mobile app
  • Difference between Power BI and "normal" Excel
  • Navigating Power BI desktop confidently: report view, data view vs model view
SESSION 3Data Visualisations & Report Design
  • Choosing visuals based on context
  • Column chart, bar chart (clustered, stacked)
  • Line chart
  • Card, table, matrix
  • Pie chart, donut chart, treemap
  • Native & custom visuals
  • Using field, format & analytics pane
  • Field column formatting and summarisation
  • Adding and customising data labels
  • Setting visual interactivity with slicers and filters
  • Top N (ranking top/bottom items)
  • Basic/advanced filtering
  • Understanding adhoc-highlighting
  • Applying conditional formatting
  • Sort one column by another column
  • Drill down and up using hierarchy
  • Creating custom tooltips for better context
  • Drillthrough page to dig deeper into data category
  • Creating bookmarks to share insights and build stories
  • Difference between Power BI dashboards and reports
SESSION 4AI Visuals In Power BI
  • Working with AI visuals in Power BI
  • Use Key Influencers visual to identify factors driving KPI
  • Use Decomposition Tree to aggregate data and drill down across multiple dimensions
  • Use insights in Bar Chart to analyse change in distribution
  • Use Q&A visual to ask questions and get visual answers
SESSION 5Sharing, Security & Data Refresh
  • Publishing to PowerBI.com
  • Sharing dashboards & reports via links or Teams
  • Managing permissions of underlying datasets
  • Scheduling data refreshes
  • Power BI service license: free, pro vs premium
SESSION 6What Is Power Query? >>> Engine To Extract, Transform & Load (ETL) Data
  • Using Power Query as an ETL tool
  • Power Query M language: Do you need to learn M?
  • Importing data from single or multiple sources
  • File: Excel, CSV, Folder
  • Database (SQL, SAP, Oracle, etc)
  • Online services
  • Other: Web, OData Feed
  • Unstructured vs structured data
  • Query Editor ribbon, Navigator pane & Preview grid
  • Query settings panel - recording of transformation steps
SESSION 7Data Cleaning & Formatting
  • Data types: Number, Date, Text, True/False
  • Remove Columns, Remove Other Columns & Choose Columns
  • Removing duplicates from a data set
  • Replacing values and errors
  • Null values: Replace, fill or filter out
  • Removing whitespaces and non-printable characters
SESSION 8Data Transformation Techniques
  • Converting (selected) columns to rows and vice versa:
  • Transpose
  • Unpivot columns
  • Pivot column
  • What are the differences? When to use them?
  • Transforming text columns:
  • Format: trim, clean, uppercase, lowercase, add prefix
  • Split column & merge columns
  • Extract texts before/after delimiter
SESSION 9Data Aggregation & Calculation
  • Summarise data with Group By (Sum, Median, Min, Max, etc)
  • Custom Columns: Create formulas for computations
  • Index Columns (row counters/sort orders)
  • Number columns statistical calculations
SESSION 10Combining Data (queries)
  • Difference between merge and append
  • Merge queries (the V-Lookup in Power Query)
  • Append queries (combining tables with the same headers)
  • Use cases of merge and append queries
SESSION 11Organising Power Query Automation
  • Best practices in organising your data sources
  • How to automate your data refresh
  • Importing multiple files located in a folder
  • Update Power Query for change in location of source data
  • Disabling load of redundant queries to enhance performance
SESSION 12Power Query Time Saving Tips
  • Copy queries to another Power BI file or an Excel workbook
  • Duplicate and reference queries
  • Preview or navigate to table objects
  • Navigate through complex query dependencies
SESSION 13DAX (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 14DAX 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 15DAX 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 16Commonly 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 17DAX 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 18Data 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 19Hands-on Power BI Project
  • Applying Power BI skills
  • Choice of dataset:
  • Sales/Finance dataset
  • Predictive maintenance dataset
  • Bring Your Own Data (BYOD)
  • Throughout the workshop, participants will work through practical exercises to enhance their knowledge of the tools available to build Power BI dashboards and reports
SESSION 20Table 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 21More 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 22DAX 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 23Building 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 24Time 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 25Relationship 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 26Selectedvalue & 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 27DAX Studio
  • Third-party tool to measure and improve the speed of DAX queries.
  • Capturing and debugging queries with DAX Studio
SESSION 28Additional 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 29What-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 30Hands-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
Course enquiriesEnquire about this course →

Your trainer.

POWER BI AND DAX SERIES TRAINING MALAYSIA
Alvin Yong
MSc, FCCA(UK)

Alvin 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 AND DAX SERIES TRAINING MALAYSIA
SoftwarePower BI Desktop — Laptop with Power BI Desktop installed

Reviews from our Malaysian clients.

POWER BI AND DAX SERIES TRAINING MALAYSIA

“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.”

Manager, Strategy & Planning, Petronas

“Alvin is an excellent trainer. He led interesting, interactive and focused discussions during the workshop.”

Business Development & Partnership Manager, eBay

“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.”

VP of Finance - APAC, Middle East & Africa, FIS Global

“Fantastic trainer in terms of knowledge and skills. Extremely friendly, helpful and patient.”

Manager, Global Credit Policy and Portfolio Risk, Standard Chartered Bank

“Good understanding and knowledge of the subject. Attentive and responsive to participants' queries. Presented the subject very clearly.”

Learning & Development Specialist, Micron Semiconductor

“The trainer is knowledgeable. He was helpful. He handled the class material well.”

Financial Planning & Controlling Manager, Arvato Bertelsmann

“Very informative. Gave an excellent count of how-to's.”

Regional Component Planner, Komatsu Australia

“Alvin is a very people person. Sound knowledge in his area of expertise. He carried out the programme well.”

CEO, Felda Prodata Systems

Related courses.

POWER BI · DAX · POWER QUERY · DATA VISUALISATION
Data Analytics & BI

Analyse and Visualise Data with Power BI Masterclass

Data Analytics & BI

{Big} Data Analytics with Advanced Excel Masterclass:

Data Analytics & BI

{Big} Data Analytics with Advanced Power BI DAX:

Ready 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
Data Analytics & BIPower BIDAXPower QueryData Visualisation
WhatsApp us