RADACAD runs premier professional training on Power BI. RADACAD training events run both in-person and remote/online. RADACAD also provides custom bespoke training for your organization. For custom training options for groups of 4 or more check out this page or contact us (firstname.lastname@example.org).
Power BI Training Options
We run training programs on our standard modules or bespoke for your group and team in a cost-effective way.
Check out our calendar of upcoming training courses which you can register through our website.
Learn Power BI at your own pace with the most comprehensive 20+ video courses training of Power BI in our academy.
Power BI Training: Power BI from Rookie to Rock Star
From 1 to 10+ days of training. including lectures and labs. Depends on the modules you enroll
Online using Microsoft Teams or Zoom, or in-person for your team worldwide
Public or private (corporate) training options are available.
The well-known worldwide training in the Power BI field, and the most comprehensive Power BI training on the planet from one to over ten days of training delivered by the well-known experts and MVPs, authors of books, and speakers of many conferences themselves. In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Components such as Power Query (Get Data and Transform), Modelling and DAX, Visualization, Power BI Desktop as the main tool, Power BI Service, Gateway configuration, and architecture. You will learn all the concepts with live demos. Expect learning best practices with great scenarios in this course. This course is designed in separate modules based on the type of audience. If you are a data analyst, data wrangler, data modeler, or data architect, or even a data scientist, this course has many things to teach you all.
This course is delivered to thousands of people all around the world, check out only a few of the recommendations at the bottom of this page, and check some of our clients.
Instructor: Reza Rad & Leila Etaati
Our trainers, Reza and Leila, are the world’s well-known names in the Microsoft BI field. They are best-selling authors of more than ten Power BI books, invited speakers to the world’s best and largest Power BI conferences, and they are both consultants dealing with Power BI challenges on real-world projects. They designed the course content themselves and delivered it to thousands of users worldwide.
What attendees will receive after this training?
- All data files and Power BI files related to demos and examples
- the Handout of the training
- access to the video training version of this course recorded separately. To be watched at your own pace. This worth $260 USD per module per user, but available complimentary to our live course attendees.
- Certificate of completion
Why this is the best training available for Power BI?
- The content is created and updated directly by Reza Rad and Leila Etaati with years of experience with Microsoft BI and Power BI products. They are authors of more than 10 best-selling books on Power BI.
- The sample of list of companies who have purchased our training program
- The amazing 5-star feedback from attendees of our training shows the level of learning.
- This is the only Power BI training program in the world covering 7 modules and all aspects of Power BI learning for up to 10 days of training.
Our customers share their stories
“We organized a 4-day training event with Reza and Leila from Radacad. From the outset, they provided great advice on what they could cover and provided a very comprehensive overview for the 25 trainees. For anyone who is considering PowerBI specifically and want clarity on the best architecture and how to effectively use PowerBI, I strongly recommend contacting Radacad. They gave us a real head start.”
Team leader BI Training and Support Massey University
“Reza and Leila with RADACAD were phenomenal course facilitators and very accommodating of our group of 12. The course content was clear, well-paced, and structured intently with each lesson serving as a building block for the next. The team thoroughly enjoyed the engagement and definitely felt like Power BI Rockstars coming out of each session. Our organization has already begun to reap the benefits of this investment with RADACAD”
Senior Support Engineer, Microsoft
“Reza explains things so well and easy to understand and goes at a pace everyone can follow. He also reads the room and can tell if we like or don’t like something and adjusts so that it makes sense to us.”
Global Production Analyst, Zespri International Limited
Power BI from Rookie to Rock Star
This is the most comprehensive course for Power BI on the planet which is split into modules. You can enroll in any of these modules separately or take the whole course. Modules are designed independently, which means each module can be taken regardless of the order of modules. Here are a list and detailed agenda of each module:
- Module 1: Power BI for Data Analysts (2 days)
- Module 2: Data Modeling with Power BI (1 day)
- Module 3: Power Query: Get data and transform (2 days)
- Module 4: DAX and Calculations in Power BI (2 days)
- Module 5: Power BI Design Patterns (1 day)
- Module 6: Power BI Architecture (1 day)
- Module 7: Power BI for AI and Data Scientists (2 days)
Module 1: Power BI for Data Analysts (Essentials and Visualization) – 2-days course
This training is designed for data analysts, who have the data modeled and ready for them to visualize. In this training, you will learn detailed visualization practices of Power BI.
In this two days training, you will learn all components of Power BI needed for building Power BI reports and dashboards. You will learn about components such as Get Data (Power Query), Modeling and DAX, and Visualization. You will also learn about the Power BI service (website), and components that are needed to share your reports with others.
The training continues with more focus on Visualization components such as different types of charts and visuals and scenarios to use them. You will also learn about advanced visualization techniques such as designing mobile reports, changing the interactivity of visuals, detailed learning of slicers and filters, creating parent-child reports, etc.
At the end of this training, you will be able to do all visualization requirements for Power BI and will understand all other components of Power BI and how these are all working together.
The training includes but is not limited to the topics below:
1.1: Introduction to Power BI
Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through the basics of this product and introduce all components of Power BI (Power Query, Power Pivot, Power View, and Power Q&A). You will see some demos and introductions about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy it to use is Power BI in some scenarios.
- Introduction to Power BI: What is Power BI?
- Power BI Desktop; The First Experience
- Power BI Website; You’ll Need Just a Web Browser
- Introduction to Power BI Components: Power Query, Power Pivot, Power View, and Power Q&A.
1.2: Getting Data and Transformation
Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on the cloud. In this section, you will learn how the Get data experience in Power BI is and how you can transform the data in a way to get it ready for modeling.
- What is Power Query: Introduction to Data Mash-Up Engine of Power BI
- Different versions of Power Query
- Power Query Introduction
- Query Editor
- Transformation GUI
- Get Started with Power Query: Movies Data Mash-Up
- Power BI Get Data from Excel: Everything You Need to Know
- What is the Role of Power Query in a Power BI Solution
1.3: Data Modelling and DAX
Data Modelling in Power BI is an in-memory-based technology. You will learn about the structure of modeling in Power BI, and you will learn the importance of relationships and their direction. You will also learn about calculations in Power BI and how to write them. DAX is the Data Analytical eXpression language. DAX has a similar structure to excel functions, but it is different. In this module, you will learn DAX about DAX too.
The content that you will learn in this module includes but is not limited to;
- Power BI xVelocity engine basics and concepts
- Relationships in Power BI
- Hierarchies and Formatting
- Sorting by other columns
- Date Table
- Introduction to DAX
- Calculated Columns, Measures, and Calculated Tables
1.4: Data Visualization
Data Visualization is the front end of any BI application; this is the user viewpoint of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data. In this module, you will learn conceptual best practices of data visualizations which are valid through all data visualization tools. You will learn Power BI visualization skills. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with Power BI Desktop.
The content that you will learn in this module includes but is not limited to;
- Power BI Desktop Visualization
- Formatting Visuals in Power BI Desktop
- Basic Charts and Visuals in Power BI
- Sorting, Filtering, and categorization
- Custom Visuals in Power BI Desktop
- Maps and Geo-Spatial Visualization
1.5: Power BI Service
Power BI components can be deployed into different environments such as the Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations, and requirements required for each environment. You will learn how to deploy your Power BI files into the Power BI website. You will also learn how to build dashboards on the website, and how to work with Mobile Apps for Apple, Android, and Windows Phone.
The content that you will learn in this module includes but is not limited to;
- Basic Sharing and using workspaces in Power BI web site
- Dashboard vs. Report
- Power Q&A
- Mobile Dashboard Design
- Row Level Security
- Schedule Refresh vs. Other types of connections
- Gateway’s Role in the Service
1.6: Visualization Basics
Visualization is an important part of any BI system. In Power BI, Visualization plays a critical part. In this section, you will learn about why visualization is important, with few basics of visualizations such as comparing stacked vs. clustered charts. You will also learn in this section about data exploration techniques such as drill down/up, using pages, etc.
- Importance of Visualization
- Basic Charts; Bar and Column Charts
- Clustered vs. Stacked Charts
- Q&A for Reports
- Quick Measures in Power BI
- Drill Down and Up using visuals and hierarchies
1.7: Slicing and Dicing
The importance of slicing and dicing data in Power BI is critical. Power BI is not a visualization tool only, but it is also a data exploration tool. There are several ways you can filter the data. In this section, you will learn the difference between filters and slicers. You also learn about different scopes of filtering, as well as filtering modes. For slicers, you will learn different types of slicers and some advanced features such as syncing slicers through different pages.
- Scopes of Filters: Visual Level, Page Level, Report Level
- Modes of Filtering: Basic, Advanced, Relative, Top N
- Drill through Page Filter: Creating master-details page scenario in Power BI
- Types of Slicers; Date, Text, Number
- Slicer Formatting
- Syncing Slicer between Pages
1.8: Advanced Visualization in Power BI
Know that you know about visualizations and slicing and dicing, it is a good time to talk about some advanced techniques which will take your visualizations to the next level in Power BI. You will learn about techniques such as changing the interaction of visuals, report page tooltips, Bookmarks, and dynamic visualizations in this section.
- Report Page Tooltip
- Controlling the interaction of Visuals in a page
- Designing Power BI reports for Mobile
- Grouping and Binning
- Bookmark; saving the state of a page
- Buttons; action items
- Selection pane; visibility control in Power BI
- Golden triangle; Bookmark, button, and selection pane
- Colour theme or pallets in Power BI
1.9: Best Practice Scenarios of Using Visuals
It is a good time to learn about all built-in visuals in Power BI now. It is important to know which visual should be used in what scenario. You will also learn in this section about the pros and cons of each visual. You will learn specific features of the visual that can help to solve real-world scenarios. All the examples will be hands-on.
- Line Chart; Trend Analysis
- Combo Charts; Different Scales in one graph
- Ribbon Chart; Ranking
- Waterfall Chart; Cashflow
- Scatter Chart; Storytelling with the data
- Grouping charts: Pie, Donut chart, and Treemap charts
- KPI visual
- Card Visual
- Table and Matrix; showing the numbers with conditional formatting
1.10: Geo Location Visualization: Map Visuals
In Power BI, there are multiple ways of visualizing items on the map. In this section, you will learn about all the built-in ways of using map visuals in Power BI, the pros, and cons of each method, and special considerations for some of the visuals.
- Map Visual; Using Bing to find spots
- Fine Tuning Address Details
- Map Visual; Using Latitude and Longitude
- Filled Map; Regions and Polygons
- Data Category importance for the filled map
- Shape Map; Having custom map visual
- ArcGIS Map; Map visual which can do everything
1.11: Custom Visuals
In addition to the built-in list of Power BI visuals, you can leverage some of the third-party visuals created by other companies called Custom Visuals. Creating custom visuals is a developer topic, which we won’t discuss in this training, but you will learn about how to use custom visuals. You will learn about the most useful custom visuals in Power BI through many demos.
- Adding and using a custom visual
- Infographic Designer
- Tachometer Gauge
- Map custom visuals
- Certified custom visuals
- Hierarchy Slicer
- Play Axis
- R Custom Visuals
Module 2: Data Modeling with Power BI – 1-day course
This training is for someone with basic knowledge of Power BI, who wants to connect to data sources and build a data model for visualization. Data modeling concepts such as the best practice of designing data tables and how to build a data model that performs fast, building star-schema, and fact and dimension tables are all explained in this training.
At the end of this training, you will learn how to build a star schema from a data source that covers the requirement. You will understand the concept of Power BI relationships and can build a data model based on best practices.
This training includes but is not limited to the topics below;
Understanding the relationships is one of the most essentials learning to build a data model. This training starts with an explanation of why relationships are needed, what are different types of relationships, and attributes such as the direction or cardinality of the relationship are covered with examples.
- Why relationships in Power BI
- one-to-many, many-to-one, many-to-many, and one-to-one relationships
- What is the direction of the relationship?
- role-playing dimension and inactive relationships
- relationships based on multiple columns
2.2: Dimensional modeling
In this section, you will learn what is star-schema, and what are fact and dimension tables. What are different types of fact tables, and why the star-schema design is important for data modeling? You will learn the principles of data modeling.
- Why data preparation
- What is dimension table?
- What is fact table and different types of it?
- Do you need a date dimension?
- Power BI default or custom date table
- What is star-schema?
2.3: Star schema in action
After learning the concepts of star-schema, it is time to put that in action and learn how you can build a star-schema model in Power BI using techniques of data transformation and the concepts of modeling.
- Combining dimension tables
- Creating shared dimension
- Combine tables or create relationships?
- What fields to hide?
- Build your first star-schema
- One dimension filters another dimension
To have better modeling, you need to have an understanding of DAX and the possibilities of that in Power BI. This section gets you familiar with DAX and other calculations in Power BI.
- DAX or M?
- Row-based calculation or aggregations?
- Calculated tables
- Measures Vs. Calculated columns
2.5: Better data model
Finally, there are important tips to consider to take your data model to the next level, which is covered in this section of the training.
- Move shared tables to dataflows
- Shared datasets and how to use it
- Reducing the size of the model
- Important consideration about dates in Power BI
Module 3: Power Query: Get Data and Transform – 2-days course
This training is designed for data wranglers, data developers, or data transformers, who have the raw data coming from one or more data sources and want to make it ready for further modeling and analytics. In this training, you will learn detailed data transformation practices of Power BI using Power Query.
In this course, you will learn all things about Power Query, from zero to hero. You will learn Power Query from a basic level with Getting data from different data sources. You will learn about different types of transformations available in Query Editor. Table transformations such as Pivot and Unpivot will be discussed, as well as specific column transformations such as date column transformations. You will also learn about M (Power Query Formula Language) in deep. Unique features such as error handling, generators, structured columns, custom functions, and many other advanced level features of Power BI data transformations will be explored through hands-on labs and lectures.
After this course, you will be able to implement any type of data transformation through Power Query in Excel or Power BI. You will be able to work through your raw data and make it ready for modeling and analytics.
The training includes but is not limited to the topics below:
3.1: Get Data
In this section, you will learn about Power Query basics which start with Getting data. You will learn that Power Query is the data transformation tool in Power BI. You will learn different parts of the Query Editor through an example of using Power Query to transform a dataset.
- Introduction to Power Query
- Query Editor
- Get Data from Web
- Basic Transformations
- Get Data from Excel
- Use First Row As Headers / Use Headers as First Row
- Get Data from SQL Server
3.2: Data Types and Data Structures
Before going any further in learning Power Query, you need to understand data structures and data types. There are three main data structures in Power Query; table, record, and list. You will learn about these types through an example of getting data from a JSON structure. You will also learn about data types and their differences.
- Base data structures in Power Query
- Get Data from JSON
- Transforming Table, Record, and List
- Data Types in Power Query
- Query Operations
- Enable Load; Performance Boost
- Query Operations; Duplicate, and Reference
3.3: Combine Queries
One of the most common data transformations is combining datasets. Depends on the types of datasets and the way that they are related to each other, you may want to merge or append them. In this section, you will learn why you need to combine data at first, and then you will learn about scenarios that you combine data in Power Query.
- Dimensional Modelling; Designing the data model
- Append, creating a single big query of the same structure
- Merge; Joining queries when the structure is different
- Join types in Merge
- Tips to consider after Merge or Append
3.4: Better Power Query Editor Experience
To get the best experience with Power Query Editor, you need to consider organizing your queries and steps in the right way. In this section, you will learn about actions you can do on steps, such as moving them up or down, splitting steps in a query, etc. You will also learn about organizing your queries in groups (folders).
- Groups; Folders in Query Editor
- Steps Operations
- Splitting query steps
- Moving steps up or down
- Add as new query / Drill Down
- Be Careful of Actions; Undo!
3.5: Reducing Number of Rows; Filtering
Filtering rows in Power Query is an important transformation especially when the dataset is big, or when the data needs to be cleaned. There are different ways of doing filtering in Power Query. You will learn about ways to remove some rows from the top or bottom of the table, and ways that you can filter a data table based on criteria. You will learn about basic filtering and the difference between that with advanced filtering, and potential challenges that you may have through this process.
- Row Operations; Removing rows
- Row Operations; Keeping rows
- Remove/Keep Errors
- Remove/Keep Duplicates
- Using Remove/Keep combination for troubleshooting report
- Filtering based on Individual values
- The dilemma of the basic filtering
- Advanced Filtering
3.6: Column Operations
A data table in Power Query can get big if you don’t care about columns. In this section, you will learn actions that you can do on columns, and what are best practices to make sure you have the best performance in your Power BI model considering columns in your tables. You will also learn about some generic column operations and transformations.
- Column Operations
- Choosing Columns
- Removing Columns
- Data Type Change
- Locale consideration for the data type
- Replace Values
- Fill Down/Up; Very Useful for Excel
3.7: Table Transformations
Some of the most important table transformations will be explained in this section. You will learn about a way to change the granularity of a table; Grouping. You will also learn scenarios that in grouping data can be more than a simple transformation. You will learn about transformations such as Transpose, Pivot, and Unpivot, and the difference of all these items with scenarios of using them on real-world datasets.
- Group By; Changing the granularity of the data table
- Group by Advanced
- Scripting and Group by; First and Last item in each group
- Transpose; rows to columns and reverse
- Pivot; changing the name-value structure to columns
- Unpivot; changing the budget column structure to rows
3.8: Text Transformations
When you work with text values, there are many transformations you can apply. Transformations such as a split column, removing part of a text, or adding a prefix or postfix to it, concatenating some columns together, etc.
- Split Column by Delimiter
- Split Column by number of Characters
- Split into rows instead of columns
- Merge (Concatenate)
- The difference between Clean and Trim
- Parse (XML or JSON)
- Extract part of the text
3.9: Numeric Transformations
You will learn in this section how to do numeric transformations. We will talk about standard transformations such as divide and integer-divide. You will also learn about transformations such as rounding, statistics transformations, and even some scientific transformations and use cases for those items.
- Standard transformations; Divide, Integer-divide, Multiply, Add etc.
- Scientific transformations; logarithm, power square, etc.
- Statistics transformations;
- Information functions; Is Even, Is Odd, and Sign.
- Dealing with faults in Numeric calculations
3.10: Date and Time Transformations
There are many transformations applicable to date and time columns. You may want to fetch the year part of a date or get the fiscal calculation of a date. You may want to calculate age based on the birthday or calculate the difference between two dates. You will learn all these items in this section, you will also learn about time zone consideration when working with the current date and time in Power BI, and how you can resolve it in Power Query.
- Date Transformations (Year, Month, Quarter, Week, etc.)
- Extending Fiscal Date Column
- Time Transformations (Hour, Minute, Second, etc.)
- Adding Time/Date banding
- Duration Transformation and Data Type
- Age Calculation
- Local Date or Time
- Time zone consideration for Power BI
3.11: Structured Column Transformations
When you combine tables, you get a structured column as a result, which can be a table, list, or record in every value. There are several transformations you can apply to structured columns, which you will learn in this section. You will also learn what may be the potential issue with some of these transformations.
- What is a Structured Column?
- Expand and Aggregate: Performance Consideration
3.12: Add Column Transformations
There are two types of transformations in Power Query; Transforming an existing column, or adding a column based on a transformation. In this section, you will learn about these two types, their differences, and few other transformations that we have available in the add column tab of the Power Query Editor through some examples.
- Add Column vs. Transform?
- Add Column with a Transformation
- Index Column: Row Number
- Conditional Column
- Add Column by Example; When you don’t know which transformation to use
- Add Custom Column: Generic
3.13: Functions and Parameters; Dynamic Power Query
Power Query is a powerful tool for data transformation. This power can be amplified even more if you can make your queries dynamic. Instead of repeating several steps for similar data sources, you can create a function from those steps, and run that function for all other sources. Functions get parameters as the input. Functions and parameters can make everything in Power Query dynamic. If you want to learn Power Query advanced deep dive, this is the section to go through.
- Defining Parameters
- Using Parameters in an existing query
- Advanced GUI for parameters
- Creating Function from a query
- Invoking the sample function
- Add Column Transform: Invoke Custom Function
- When the advanced GUI does not exist
3.14: Power Query Formula Language: M
The heart of Power Query is a scripting language named Power Query Formula Language or M. If you want to be a good data wrangler or data developer with Power Query, you must learn M scripting. The good news is that M scripting is not a hard language to learn. This section goes through the basics of the language, data types, literals, and everything is needed for understanding an M script’s structure.
- What is M? and the importance of learning M
- M Syntax
- End of the line
- Variable Names
- Special Characters
- Escape Character
- Step by Step Coding
- Function Call
- A real-world example
3.15: Working with Data Structures in M
As you are dealing with data in Power Query, it is important to learn how to work with tables, lists, and records from the code. In this section, you will learn about these three structures in code, and how to navigate between different parts of each structure.
- Primitive Value
- Navigating through List and List functions
- Navigating through Record and Record functions
- Navigating through Table and table functions
- Concatenating lists and records
3.16: Advanced M Scripting
Now that you know more about M scripting, it is time to see how powerful this part of Power Query can be compared to the graphical interface of query editor. In this section, you will learn features that you have access to apply using M scripting. You will learn ways to get a list of all functions, doing error handling in an advanced way. Applying some changes in functions and parameters which is only possible through the code. You will also learn an end-to-end example using everything you learn about M at the end.
- #Shared Keyword; function library of Power Query
- Parameters in the code
- Custom Functions through scripting
- Error Handling in Power Query
- Generators in Power Query: Implementing Loop Structure
- EACH: singleton function
- Sample Custom Function: Day Number of Year Custom Function
3.17: Performance Tips and Tricks for Power Query
Some transformations or operations need careful attention in Power Query. In this section, you will learn about performance tips and tricks for Power Query to make sure you have always a good performance-tuned data transformation logic. Each sample will be discussed through the live demo in this section.
- Enable Load; Simple, but Efficient
- Reducing Number of Columns
- Query Folding
- Grouping and Aggregation; Performance Consideration
- Merge; Before and After, things to Consider
3.18: Error Handling
In any data-related solution, you should expect bad data rows to appear. If you haven’t thought about the appearance of bad data rows and you just did the transformations considering everything will be nice and tidy, then you may face many errors in Power Query. This section is all about how to handle errors, deal with bad data rows, create troubleshooting reports, etc.
- Keep/Remove Errors; Troubleshooting report
- Count Rows
- Replace Errors
- Data Type considerations
3.19: Use Cases
At the end of the training, we go through some end-to-end solutions using Power Query. These solutions leverage everything you learned through the training about this tool and language; you will see how all those parts come to help together to build the solution. We will go through building a date dimension that has all calendar columns, fiscal columns, and public holidays fetched live, and we will talk about combining files from a folder.
- Date Dimension with Power Query; building the base table
- Adding Fiscal columns to the Date dimension
- Getting public holidays live and merging to the date dimension
- Looping through files in a folder with Power Query
Module 4: DAX and Calculations in Power BI – 2 days course
This training is designed for data modelers, who have the data prepared to be modeled for analysis. In this training, you will learn detailed calculations of Power BI, and DAX unleashed.
In this two days training, you will learn DAX from zero to hero. You will learn about DAX which is the data modeling expression language in Power BI (and SSAS Tabular, and Power Pivot). You will learn from Simple DAX calculations to complex expressions and calculations for solving real-world challenges of a BI solution.
The training continues with more focus on DAX; we will talk about the evaluation context in DAX, which defines the mindset and the way of thinking when you are writing DAX expressions. You will learn about DAX function categories such as Aggregation functions, Iterators, Filter functions, parent-child functions, time intelligence functions, functions dealing with relationships, etc. You will learn all scenarios through hands-on examples of real-world data.
At the end of this training, you will be able to write complex DAX expressions for your analytics need, and put them all together to build the best model for your data analysis solution using Power BI.
The training includes but is not limited to the topics below:
4.1: Introduction to DAX
DAX is an abbreviated name for Data Analysis eXpression language. This is the expression language in Power BI for analytics. DAX is a dynamic expression language that will consider the interaction of the user at the time of visualization. Using DAX, you can do calculations such as year to date, year over year comparison, etc. Most of the data modeling training is about DAX. In this section, you will learn the basics of DAX.
- Syntax of DAX
- Naming in DAX
- Operators and Operands
- Logical Operations
- Data Types in Power BI Model
- Overview of Functions in DAX
- DAX or M? When to use Which?
4.2: Calculations in Power BI
There are three types of calculations in Power BI. Calculated Column, Measure, and Calculated Table. You can write DAX expression in all these three types of objects. This section will teach you what the main difference between the calculated column, measure, and the calculated table is, and what are scenarios of using them.
- Calculated Column; Row by Row
- Measure; Single Output
- Calculated Table; A derived table
- Calculated Column? Maybe a good candidate for Power Query transformation
- Measures are Dynamic
4.3: Aggregation and Iterator Functions
The first set of important functions in DAX are aggregation functions. There is a set of normal aggregation functions such as SUM, MIN, MAX, and there is another set called iterators. The way of working with iterators is different. Iterators get an input table and an expression. An example of an iterator function is SUMX. In this section, you will learn the difference between SUM and SUMX and scenarios of using those two.
- Aggregation Functions
- Implicit Measures vs. Explicit Measures
- Sum of an Expression: SumX
- Iterator Functions
- Difference between SUM and SUMX
4.4: Filter Functions
Filter functions are probably the most important functions in DAX. You can refer to a column in DAX (like Excel), but you cannot refer to rows. If you want to refer to rows in an expression, you must filter it, and that is why Filter functions are important. There are several filter functions, and the behavior of all of them is unique. In this section, we will talk about some of these functions through real-world examples.
- Filter Functions to be used inside other functions
- Examples of using ALL function
- ALL and SUMX; Percentage Calculation
- Filter Function: Custom Filter
- Values/Distinct; getting a unique list of values
4.5: Evaluation Contexts and Conditional Sum
Understanding Evaluation contexts are one of the most critical learnings in DAX. The evaluation context refers to the way that filters impact the calculation’s result in DAX. There are two types of contexts; Row context, and filter context. In this section, you will learn about the difference between all these, and you will learn scenarios that you need to be careful of when the context changes.
- Row Context
- Filter Context
- Exception for Row Context
- Exception for Filter Context
- Calculate Function
- Conditional Sum; Three ways of Implementing; pros and Cons
- Variables in DAX and using them for debugging
4.6: Relationship Functions
Some of the functions in DAX are going through directions of relationship and apply some filtering based on that, like RELATED() for example. Some other functions change the behavior of relationships such as the UseRelationship() function. In this section, you will learn about relationship functions in DAX and scenarios of using them.
- Related: Many to one
- RelatedTable: Sub table that can be used as a filter
- CrossFilter: Changing Direction of relationship
- UseRelationship: using an inactive relationship
- TreatAS function
4.7: Time Intelligence Functions
Calculations based on time and date are critical for many businesses such as finance. You can use DAX to do calculations such as year to date, fiscal year to date, year over year comparison, and rolling 12 months average. In this section, you will learn some basic time intelligence functions such as TotalYTD to a calculated year to date. You will also learn about scenarios when you do not have the built-in function for your use case and will learn how to write the combination of function usages in DAX to achieve the solution.
- Choosing the Date Table: Built-in or Customized Date Table
- Mark as Date Table
- Year to Date, Quarter to Date
- Fiscal Year to Date
- Same Period Last Year
- Year over Year Comparison
- ParallelPeriod vs SamePeriodLastYear vs DateAdd
- Running Total
- Rolling 12 Month Sales
- Average 12 Month Sales
- Rolling 6 Months
- Flexible time banding
- Time zone consideration in Power BI
4.8: Dynamic DAX: Parameters
DAX calculation is dynamic based on the user interaction in a Power BI report page. However, you can take a step further, and make the expression of DAX even more dynamic. The user can change a value which is defined statically in your DAX expression using a parameter. Parameters will make your DAX expressions even more dynamic. In this section, you will learn about parameters, and their usages, and the scenario of using a parameter table to select from multiple measures dynamically.
- Numeric Parameter Definition through GUI
- GenerateSeries DAX function
- SelectedValue DAX function
- Sample Scenario: Customer Retention with Dax and Power BI
- Other Types of Parameters? Parameter Table
- Sample Scenario: Selection of Measures in a table dynamically
4.9: Parent-Child Functions
DAX can navigate through a hierarchy with an unknown number of levels. An example of such a hierarchy is a chart of accounts or organizational hierarchy. In this section, you will learn about parent-child functions which can be used for organization hierarchy. You will learn different scenarios of using these functions in real-world examples.
- Organizational Hierarchy or Chart of Accounts: unknown levels
- Path function
- PathLength: getting the number of levels
- PathItem: finding a specific level
- PathContains: Security Pattern
- LookupValue: To get the other related fields
4.10: Best Practices Tips and Tricks
Performance considerations when doing the modeling in Power BI is the last but not least important part of this course. You will learn about tips and tricks that keep your model performing well even with a huge number of data rows. In this section, you will also learn about other tips that make the maintenance of your model easier, such as using a tool like Power BI Helper.
- Modelling Best Practices
- Understanding when to switch between Power Query and DAX
- Considerations for very large tables
- A tool that helps: Power BI Helper
Module 5: Power BI Design Patterns – 1-day course
Power BI Design patterns are a set of architectural designs of the dimensional models combined with Power Query techniques and DAX calculations that are designed for solving a business case scenario. The technique of a design pattern can be applied with minimal changes to similar patterns in other scenarios. An example of a design pattern is a subscription system. As a data analyst, you like to know the count of active subscribers at any given date. A model designed for this practice with the DAX calculations can be useful for a ticketing system too. Because you will likely want to know how many open and closed tickets you have on any given date. This training module is recommended for anyone building Power BI data models.
This course includes but is not limited to the design patterns above;
- What are design patterns and how to use them
- Customer retention
- Inventory stock on-hand
- Age-based analysis
- Static banding or binning
- Dynamic banding or binning
- Budget Vs. Actual
Module 6: Power BI for Data Architects (Architecture and Administration) – 1-day course
This training is designed for a data architect or administrator, who is designing the architecture of leveraging Power BI in a solution. Someone who wants to understand how all components of Power BI are sitting beside each other to build the whole solution. This training is designed for understanding the strategy of using Power BI rather than its development.
In this training, you will learn about architecting the strategy of a Power BI solution from end to end. This is not training about development (Modules 1 to 3 already covered that). You do not need to attend previous modules to attend this course. This course is designed separately from those. However, knowing some of the basics of Power BI is helpful.
You will learn about Power BI Service, and different types of connections in Power BI and choose the right type of connection for your solution. You will also learn all the different ways of sharing a Power BI Solution and the pros and cons of each. The course continues with a detailed discussion about row-level security. You will learn how gateway configuration and setup will be in the whole package. You will also learn about integrating Power BI with other tools, and some architecture blueprints to follow when you are designing a Power BI solution.
At the end of this training, you will be able to design the architecture of Power BI for your requirement; you can choose the right way of sharing and design the gateway configuration as well as the communication of Power BI with other tools and services. You will be able to answer any questions that come to your mind about a solution architecture in the Power BI world and make the right decision to choose the right strategy of Power BI usage in your organization.
The training includes but is not limited to the topics below:
6.1: Power BI Service
Power BI service or website plays an important role in designing a Power BI solution. Power BI website is the hosting environment for Power BI reports, dashboards, and datasets. In this section, you will learn about these objects and their configuration. You will also learn about the different types of connections in Power BI, and the position of the gateway in the solution, configuring and installing it.
- Power BI Components
- Power BI Service Content; Dashboard, Report, and Dataset
- Dashboard Features in the service
- What is Gateway?
- Two types of Gateway; On-premises, Personal
- Configuration and Installation of Gateway
- Import Data and Schedule Refresh
- DirectQuery Connection
- Live Connection
- Composite Model
There are several ways of sharing Power BI reports and dashboards. Each of the methods has pros and cons and should be used in specific scenarios. In this section, you will learn scenarios to use each of these methods for sharing, and the sharing of architecture, and a comparison between all these methods at the end.
- Basic Dashboard or Report Sharing
- Workspaces in Power BI; Dev Environment
- Power BI Apps; User Environment
- Publish to Web
- Embed in SharePoint Online; Office 365 Integrated Sharing
- Power BI Embedded; Embedding content into another application
- Comparison of all Sharing methods
Sharing is about giving users access to the entire content; security is about giving them access to part of it. There are different ways of implementing security (which is called row-level security in Power BI). Statics row-level security is a good option when roles are limited items. The next level is to define a dynamic row-level security using DAX functions. Dynamic row-level security comes as different patterns which will be discussed here through examples.
- Row Level Security
- Dynamic Row Level Security
- Patterns for Dynamic Row Level Security
- Manager Level Access
- Organization Chart Access as Dynamic Row Level Security
- Profiles and Users; Dynamic Row Level Security
- Dynamic Row Level Security using SQL Server Analysis Service Live Connection
6.4: Administration and Licensing
As a Power BI administrator, you need to have a careful eye on some of the metrics and control some of the settings across your organization’s Power BI tenant. In this section, you will learn about Power BI administrator configuration options and options which are critical to controlling. You will also learn about all licensing options for Power BI and will have a clear view of what would be the best licensing option for you.
- Power BI Administrator Configuration
- Usage Metrics
- Customizing Usage Metrics
- Power BI Licensing Guide
- Power BI Premium
- Power BI Embedded Licensing
Power BI can integrate with other tools and services such as Reporting Services and Excel. In this section, you will learn about all integration options for Power BI. This section will also give you a full understanding of Power BI Report Server; the on-premises usage of Power BI. You will learn Power BI REST API capabilities (not at a developer level, more in architecture level), and you will learn about real-time streaming options available with Power BI.
- PowerPoint Integration with Power BI
- Power BI and Excel; More than just an Integration
- Power BI Report Server: Power BI on-premises
- SQL Server Reporting Services Integration with Power BI
- Real-time Streaming with Power BI, and Azure Stream Analytics
- Power BI REST API
The last part of the training focuses on architecture blueprints for Power BI. In addition to architecture best practices for sharing, self-service, enterprise-level architecture, you will learn about a tool that can help in the Power BI solution designed by RADACAD; Power BI Helper.
- Architecture for Enterprise Scale
- Architecture for Sharing
- Architecture for Self-Service
- A tool that helps: Power BI Helper
Module 7: Power BI for AI and Data scientists – 2-days training
If you want to take an extra step and have a next-level data analysis using AI functionalities in Power BI, this course is for you. This course steps out of the descriptive analysis of Power BI and steps into predictive analytics using Power BI in combination with AI features such as Azure Cognitive Services, Azure Machine Learning, R, Python and etc.
This course includes but not limited to the topics below;
- AI visuals in Power BI
- AI functions in Power Query
- R and Python in Power BI
- Power BI and Azure Cognitive Services
- Power BI and Azure Machine Learning
- Time series with R in Power BI
Contact us to talk about the training
Contact us here, we love to talk with you and your team about your training requirement and scheduling the proper training modules for upskilling your group.
Why Power BI Training?
Power BI is the newest Microsoft BI tool for data mash up, build modeling, and visualize it effectively. It supports mobile devices, it supports self-service, and along with its great functionalities, it doesn’t require heavy development or production system.
Common Myths about Power BI
There are some myths about Power BI that sometimes prevents people and organization to move towards using this product. We’ve listed some of the myths in the following. Please note that these are Myths, and they are not the right assumptions.
Myth 1: Power BI is a self-service Excel tool, and isn’t suitable for Enterprise Solutions.
There are many scenarios that Power BI that has been used in enterprise solutions. Power BI features for data mash-up (with the very powerful data transformation component: Power Query), and data modeling based on xVelocity in-memory engine (Based on SQL Server Analysis Services Tabular Engine), and great data visualization components, along with mobility support of this product, are rarely available in other products. We have designed, implemented, and have seemed many BI enterprise solutions based on Power BI within very large organizations.
Myth 2: Power BI is easy to use, and doesn’t require a training course.
Power BI is an easy-to-use tool, but for basic functions. Every BI application requires calculated measure, which needs to be written in DAX. Every BI application requires advanced data transformations which need to be handled with Power Query M scripting language. Designing and implementing solutions with Best Practices is always a requirement for reliable BI applications. We designed our Power BI training in a way that delivers advanced content for all components of Power BI and fortifies your team’s knowledge of the product, so they can solve any complex challenge with this tool.
Myth 3: Power BI is only for Microsoft based environments and platforms.
Power Query is the core of data extraction in Power BI. Power Query supports a wide range of data sources. Not only SQL Server, but also Oracle, MySQL, web service result set, DB2, and many other data sources are supported. There is also the possibility to connect to many systems such as Salesforce. The Visualization component of Power BI is also HTML5 supported and is available for all mobile devices. Power BI app in AppStore, and Google PlayStore, as well as Microsoft AppStore, enables dashboards and data visualizations to be available in all environments.
Myth 4: Power BI is not a powerful and fully functional BI tool, and cannot be compared with other tools in the market.
In fact, the reverse is true. Power BI is such powerful that can be the replacement of many other expensive BI tools in the market. We’ve been through scenarios of successful Power BI solutions that have been a replacement of Cognos, IBM products, Oracle WB, SAP BO, and so on. Power BI has 5 powerful components that cover the main aspects of BI and the data analysis system.
In addition, there is always the possibility to use this as a hybrid approach and save lots of time and money along with great features that are only available in this product.
What others say about our training and trainers (Testimonials)
Stefan Küppers, Business Process Owner BI / SCI, Logistics
Together with two colleagues of mine I joined Module 2 & 3 of the “Power BI from Rookie to Rock – Star” Training in Duisburg, Germany. The in-person training hold by Reza was just fantastic. In a well-structured way Reza not only gave a comprehensive overview but also deep dived into the functionalities of PowerBI and showed us the great value this software could bring to enterprises. I can strongly recommend the sessions and hope to get the chance to attend other courses as well. Thanks a lot to Reza and team!
Donna MacKenzie – Senior Revenue Analyst Massey University
The workshop run by Reza and Leila was amongst the best I’ve ever done. The pace was just right. Their prepared materials were superb. I really enjoyed the hands on nature of their training. I learned so much in a short time. I came back to my desk and felt confident enough to start building my own reports.
I would definitely sign up for their training again!
Brent Gamble – Team leader BI Training and Support Massey University
We organised a 4 day training event with Reza and Leila from Radacad.
From the outset they provided great advice on what they could cover and provided a very comprehensive overview for the 25 trainees attending the training. For anyone who is considering PowerBI specifically and want clarity on the best architecture and how to effectively use PowerBI, I strongly recommend contacting Radacad.They gave us a real head start.
Thanks Reza and Leila.
Daniel Kastelic – Otago Regional Council
Easily the finest quality course I’ve ever attended in my life. Reza knows PowerBI inside out and it really showed in the extent of topics covered. He was also willing to show me an example on real life data application, and offered further help via email enquiries in the future. Well worth the money and time invested, as PowerBI is definitely the future of Business reporting.
Jamie Yim – Senior Business Analyst Enterprise Process
I attended RADACADs “Power BI from Rookie to Rock – Star” course in Auckland from 19-23 Nov 2018. I had a beginner’s knowledge of Power BI, and found the course extremely helpful in understanding what could be done within Power BI. Reza was an excellent instructor, and explained concepts and methods in a clear and concise manner. There were lots of practical examples and exercises provided as part of the course, and Reza was also very helpful and willing to answer any questions we had about improving our existing Power BI reports and dashboards. A very enjoyable course – I highly recommended it.
I have been using Power BI for around 1 year and I was in love with it the very first day. It was very easy to visualize and bring our data stories to the client’s presentations. During my initial days, I had to use web search or use the Microsoft Power BI forums to find answers to my questions and to explore the tool. On many such occasions, I found Reza’s blog and articles to my rescue. I finally decided to attend the 5 day Power BI : Rookie to Rockstar Course.
Right from the Day 1 through Day 5, Reza was a professional and a pleasant instructor. He is very approachable and very much flexible even though I was attending the Online Course from United States. The classroom was LIVE from New Zealand. Very thorough in covering all the details mentioned in the curriculum and there was no Question that he could not answer. His expertise and knowledge are second to none. I have learnt a lot with the sessions especially data modeling and DAX functions which I would definitely put to use in the near future at my current job. I would like to thank Reza and his team for the flawless and wonderful sessions. It was very informative.
Daniel R Tuma, Senior Systems Analyst at Cleveland Metro Schools, Ohio, USA
I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.
Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.
After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.
I would recommend Radacad Power BI Training without hesitation.
Jeffrey Weir; Independent Consultant, Wellington, New Zealand
I learned a lot from this course: it gave a great overview of what’s possible both right from the UI, and what more awaits if you lift the hood and pimp the engine. It’s incredible to see just how much PowerBI and PowerQuery radically simplify what I would otherwise have to do with many, many lines of complicated VBA code. And Reza is a masterful user of this tool.
Note that Reza covers a lot of ground in this course, and it is pretty fast paced. While you can take a laptop and follow along as Reza takes PowerBI/Powerquery through its paces, for the one-day course my recommendation would be to keep that laptop closed, and simply sit back and watch Reza in action, so you can fully concentrate on just how simple this tool is to use in the right hands as well as think about how you might use the tool to your own ends and ask questions along those lines. You can always purchase the video course (if it’s not already included in your course) and step through the material again at your own pace.
Rob Wilby; Independent Consultant, UK
I must say your “Power BI Essentials” course has been the most comprehensive Power BI course I have found to date and seeing how you work with data especially the little tricks naturally introduced at just the right moments has made this course a pleasure to take.
Martin Catherall – Data Platform MVP, PASS Regional Mentor
I attended Reza’s Power BI pre-con “From Rookie to Rock star” at SQL Saturday Brisbane in May 2016. I was a complete Power BI novice, having only previously accomplished a few very basic things with Power BI. Reza took the group through all the components that make up environment. He explained how they had evolved and took the group through walk-throughs of each one. Reza was great an answering questions from the audience at the event and going into extra detail if necessary. All-in all, I would say that I’m now a very confident Power BI user after attending Reza’s pre-con.
Dan Cheshire, Senior Project Manager Air New Zealand
Reza does not just know Power BI like the back of his hand, he is the most commercially aware BI person I know. He understands that companies work in the real world and that data isn’t always clean and needs manipulation for it to work and be useful. When Reza worked on our project, nothing phased or flustered him, he always came up with solutions for problems that seemed to me as insurmountable. Can’t recommend him enough, invest in Reza as he will not only save you a whole heap of time, but he comes up with long lasting and very powerful Power BI solutions for your business.
Sonia Buckley, Data Analyst at Zoom2u, Sydney, Australia
We have started to use Microsoft Power BI and whilst Microsoft tutorials are helpful, if you are not used to DAX then it is very difficult to get your head around and there is little other resources that don’t cost an arm and a leg. I had a call with Reza who made my life so much easier in just under an hour! Any time spent speaking to Reza will be well worth it – I have saved hours of trying to get my head around our dashboards by getting some very useful tips and explanations on why things are done the DAX way! Not only that but he is very patient and straight forward. Highly recommended!
R. Kemp, Canada
The course was an excellent investment overall, it exceed my expectations. It was well paced and presented. The instructor, Reza Rad, was excellent and was able to answer all my questions and explain complex idea in a manner that was easy to understand and follow. I had been using Power BI Desktop for a number of months prior to taking this course, but wish I had attended it sooner! The course structure of 1 day of overall Power BI and followed by 4 days of more intensive instruction on the various parts of Power BI allowed for a basic understanding of the whole, and then more comprehensive learning. The live-on-line experience worked well technically and allowed for questions and problem-solving with the instructor. I learned a lot about Power BI and this course gave me a great foundation for using Power BI to clean, model, design, and share reports using a variety of visualizations that will provide business intelligence to my company. I would recommend this course to anyone wanting to learn Power BI either as a starting point or even after you have used the tool to deepen your understanding and build your skill-set.