Course Information
Advanced Data Modeling and DAX in Power BI
Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.
A course coordinator will email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.
Course Description
In this Power BI course, participants develop a deep understanding of Power BI’s data modeling and DAX capabilities. They explore advanced techniques for creating and optimizing data relationships, categorization, and hierarchical structures. The course also covers powerful DAX expressions, statistical functions, and best practices for enhancing performance. Through hands-on exercises, participants learn to build efficient, high-performing reports and dashboards using advanced Power BI techniques.
YB 4.23 (P)
Course Objectives
Objectives:
Understand and implement different data modeling approaches, including Star and Snowflake schemas
Create and optimize relationships, hierarchies, and groupings for efficient data representation
Develop proficiency in writing and debugging complex DAX expressions
Apply statistical and ranking functions to perform advanced analytics in Power BI
Optimize Power BI performance using best practices, query folding, and performance analysis tools
Outline:
Data Modeling in Power BI
* Understanding Data Modeling
* Star vs. Snowflake Schema: When to Use Each
* Creating Relationships: One-to-One, One-to-Many, and Many-to-Many
* Organizing Data: Display Formats, Categorization, and Folders
* Building Hierarchies for Efficient Data Navigation
* Grouping and Binning for Aggregated Insights
A Quick Overview of DAX
* What is DAX?
* Calculated measures, columns, and tables
* Using SUM and SUMX
* Using FILTER
* Using CALCULATE
Statistical and Ranking Functions in DAX
* Ranking Functions: RANKX, RANK.EQ, RANK.AVG, DENSERANK, PERCENTRANKX
* Quartile and Percentile Functions: NTILE, QUARTILE.EXC, QUARTILE.INC, PERCENTILE.EXC, PERCENTILE.INC
* Central Tendency Measures: MEAN, MEDIAN
* Standard Deviation and Variance: STDEV.P, STDEV.S, VAR.P, VAR.S
* Advanced Statistical Functions: SKEWNESS, KURTOSIS, COVARIANCE.P, COVARIANCE.S, CORREL
* ANOVA: Performing Variance Analysis in Power BI
Data Sampling and Filtering in DAX
* Using SAMPLE and TOPN for Data Sampling
* Generating Random Data with RAND()
* Filtering Techniques: SWITCH, FILTER, and ALL Functions
* Concatenating Text Data: CONCATENATE() and CONCATENATEX()
* Counting Functions: COUNT(), COUNTA(), COUNTBLANK(), COUNTROWS(), DISTINCTCOUNT(), DISTINCTCOUNTNOBLANK()
Advanced DAX Concepts
* Working with Calculation Groups in Tabular Editor
* Creating Dedicated Tables for Measures
* Using Variables in DAX for Performance Optimization
* Advanced Variable Techniques for Complex Calculations
* Debugging DAX Expressions Using DAX Studio
Power BI Performance Optimization
* Choosing Between DirectQuery, Import, and Hybrid Models
* Using Performance Analyzer to Identify Bottlenecks
* Understanding Query Folding and How It Affects Performance
* Leveraging the Best Practice Analyzer for Optimized Data Models
* Effective Strategies for Data Grouping and Summarization
Prerequisites
• Basic understanding of Power BI, including data loading and report creation
• Familiarity with fundamental DAX functions and concepts
• Basic knowledge of relational database concepts and SQL (recommended)
Instructors
Faheem Javed began his IT career as a Visual C++ programmer in the 1990s. He has closely followed the evolution of Microsoft’s .NET development, Business Intelligence, Machine Learning, Data Science, and Azure AI. Faheem has witnessed transitions from acquiring third-party tools like ProClarity Analytics to advancements in DTS/SSIS/SSAS/SSRS, SharePoint Performance Point Services, Polybase, SQL Machine Learning Services, Power BI, Azure Synapse, Azure Databricks, Microsoft Fabric, Azure OpenAI, and Copilot. He holds numerous certifications, including Azure Solutions Architect and Azure Data Engineer, and has conducted both standard and custom training on these technologies. Faheem also teaches Power BI and Data Analysis with Ascendient Learning
Additional Information
TAC Rule 523.142(g) requires the CPE Sponsor to monitor individual attendance and assign the correct number of CPE credits. Participants will be asked to document their time of arrival and departure in compliance with this Rule. Additionally, attendance will be monitored throughout the day and CPE certificates will reflect actual attendance of each participant.
If you are making travel plans to come to Austin, we recommend making "refundable" air and hotel reservations or waiting until 14 days before the class to actually book your reservations. Courses are occasionally canceled or rescheduled due to low enrollment. We determine whether a course has enough participants 16 days prior to the course date. If we cancel or reschedule, we will email the participant and his or her billing contact no later than 14 days before the original class date.
To see answers to our Frequently Asked Questions, visit Texas State Auditor's Office - Professional Development FAQs