Highlights
- Master advanced DAX techniques
- Create smart, interactive visualisations
- Utilise Power Query for complex data transformations
- Experiment with M Language for custom queries
- Explore data modelling concepts
- Understand Power BI publishing and architecture
- Custom course options available to suit your specific needs.
Course Details
DAX
- 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
Smart Visualisation
- Make visuals more flexible using Field Parameters
- Options with Scatter Chart, Waterfall Chart
- Play axis
- Trend lines
- Gauge and KPI visuals
- Creating a Tooltip popup page
- Set custom Page Size
Advanced Power Query
- The Power Query Environment and M language
- Stepped Data Transformation
- Removing or hiding data model redundancies
- Disable loading of intermediate queries not needed for visualisation
- Create a custom table with Enter Data
- Merge queries
- Append queries
- Transform and Split columns
- Add custom columns
- Import text dates from a non-UK locale
- Derive and visualise Durations
- Advanced Search and Replace
- Auto-import all sheets from an Excel workbook
- Auto-import all files from a folder
- Accommodate inconsistent column names with a column mapper
M Language
- M Query Basics
- Advanced Editor
- M Variables and data types
- Strengthen code with custom M variables
- Understand the power of M Functions
- Create and use an M function to change data values
Data Modelling
- Normalised vs Denormalised data models
- Reapportion numerical data from Header to Detail level granularity
- Understand filter flow direction in a Star Schema
- Use Power Query to build Dimension and Fact tables from normalised data
- Reduce Fact table rows by reducing granularity
Power BI Publishing Architecture
- What Gateways do
- Gateways: Personal vs Enterprise
- Gateways and Direct Query
- Scheduling Data Refresh
Options For Custom Courses Only
- Incremental Refresh using Power Query Parameters
- Implementing and testing Row Level Security
- Using the R and Python Visuals
- Gannt chart
- Unpivoting a pre-pivoted data source
- Admin topics
Who should attend
- Business Analysts
- Financial Analysts
- Quants
- Data Scientists
- Staff who already use Power BI or have attended our introductory course and wish to explore its features in more depth
- Developers and Super Users/Power Users looking to maximise their use of Power BI
Note: This course requires delegates to use Power BI Desktop. Mac users must make arrangements to use a PC.
Feedback
4.8 out of 5 average
"Absolutely excellent, extremely helpful to plug gaps in my knowledge and learn the logic behind Power BI and DAX. All explanations were extensive and now much more confident about Power Query, M language and understanding DAX - NHS, Snr Perfomance Analyst, Power BI Adv, Aug 2023
“Learning more about the query editor was very useful and especially the merge tables features. The presenter was very helpful and had great knowledge of Power BI." AG, Business Analyst, Power BI Advanced, Feb 2021