Please complete the following exercises, in the suggested order, before watching this video in which Alberto explains how to solve them. Best of all, Try to complete them when you are confident with all the topics covered in the course, as these almost-final exercises cover a lot of different DAX topics.

Please complete the following exercise before watching this video in which Alberto explains how to solve them:

• 15.10: Static segmentation

You can download all exercise files from here (only available to Full Access Subscribers).

Please complete the following exercises, in the suggested order, before watching this video in which Alberto explains how to solve them:

Please complete the following exercises, in the suggested order, before watching this video in which Alberto explains how to solve them:

Please complete the following exercises, in the suggested order, before watching this video in which Alberto explains how to solve them:

Please complete the following exercises, in the suggested order, before watching this video in which Alberto explains how to solve them:

The Mastering DAX course includes many exercises that you should complete during the course. You can also download demo code and data, and all the presented slides that you saw in the videos. There is almost 700 MB worth of data for Full Access Subscribers—scroll down to access the download once you have logged in.

When you need to work with tables storing data at different levels of granularity, it is important to use the correct technique. In this final module of the course we analyze a budgeting scenario with data stored at different levels of granularity, solving the scenario step-by-step.

In this module we analyze an advanced type of relationship: many-to-many. Knowing how to handle many-to-many relationships proves to be very useful as an advanced modeling technique. There are several ways of handling many-to-many relationships.

In this module you will learn the difference between a base table and an expanded table. Expanded tables are useful for advanced calculations. Moreover, a deep understanding of expanded tables proves useful for understanding the importance of always filtering columns instead of tables.

You can use DAX to create calculated columns and measures, but you can also use DAX as a query language.

Almost any data model includes some sort of a calculation related to dates. DAX offers several functions to simplify these calculations, which work fine if the underlying data model abides by certain specific requirements.

Many DAX functions, including all the aggregate functions, iterate a table executing a DAX expression for each row in a specific row context. When using these functions, called iterators, it is important to control the granularity of the calculation in case a context transition is executed for each iteration.

CALCULATE is by far the most important, useful, and complex function in the DAX language, even if the function seems simple by itself. It only performs a few tasks, but the number of scenarios where CALCULATE is necessary, along with the complexity of the formulas that can be written with CALCULATE, make it necessary to dedicate a full, in-depth learning module to it.

The difference between scalar and table functions is that the former returns a single value, whereas the latter returns a table of one or more columns and one or more rows.

In Alberto’s words: DAX is simple, but it is not easy. There are only a few concepts that you need to learn, but you need to learn them well, in-depth, to become proficient. A small investment of your time into learning the beatiful DAX language will reward you with many years of productivity.

This interview with Rafal Lukawiecki and Hugh Simpson-Wells discusses applications of machine learning for IT security purposes, including: authentication, authorisation, log analysis, and breach prevention.

Artificial Intelligence of today easily becomes Artificial Stupidity. This short video explains its dangers, and suggests when it is OK to use AI.

This video contains some thoughts about what 2019 holds for Power BI, based on various announcements made by Microsoft during 2018, especially its focus on enterprise features.

This video introduces Azure Analysis Services: a cloud-based version of SQL Server Analysis Services, which is essentially the same database engine that Power BI uses but available as a separate service, enhanced with sophisticated development tools and a rich security model.

This short video explains how to keep your Power BI report up-to-date if you are not using DirectQuery or Live Connections—imported data needs a scheduled refresh.

This video shows the Power BI Publisher for Excel add-in to connect Excel on the desktop to data stored in Power BI, and the Excel Cube Formulas, which are more suited for highly-structured reports.

This video introduces report building in Power BI, both for desktop, cloud, and mobile use.

This video introduced new features in Power BI that have been added in 2018 and that are relevant to the DAX and the Data Model section of the course.

This video discusses new features in Power BI that are related to the Modelling Data module of this course.

Everything that happens in the Query Editor is translated, behind the scenes, into a language called M. M is a functional language designed specifically for data loading and transformation.

Once you have connected to your data sources you can use the functionality of the Query Editor to transform it so that it is in the format you need it for Power BI - which is typically a dimensional model.

This video contains new content that is related to the Connecting to Data Sources section, and covers new features that have been added in late 2018.

Power BI offers a number of different licensing options, all of which are discussed in this video.

This short video explains how we have updated the Power BI course modules with the new features that were added during 2018.

In this final, 1-hour video of our SSAS cube design course, Chris Webb tackles the goal we all aspire to: performance, which is directly under your control when you build aggregations. You will learn about different ways to build and tune them, and how to validate if they are actually providing any benefits to your queries. You will also find out how to balance performance goals achieved while querying with any degradation of cube processing time. As usual, you will see plenty of detailed demos, even focusing on the minutiae of such aggregation design concerns as many-to-many relationships, semi-additive measures, partitioning strategies, parent-child hierarchies and MDX calculations.

This 30-minute video by Chris Webb discusses the operational side of cubes: deployment, processing, backup and restore, synchronization, and scripting.

Following on from the module on designing dimensions, Chris Webb teaches how to build cubes, measures, and measure groups, in this 1-hour 10-minute, in-depth, hands-on tutorial. You will also learn about various dimension-measure relationships, measure expressions, Distinct Counts, semi-additive aggregations, important properties such as FormatString, actions, like a drill down, perspectives and translations.

SQL Server Data Tools-BI (SSDT-BI, previously called BI Development Studio or BIDS) is the name for the Visual Studio templates used for building SQL Server Analysis Services projects, which are fully introduced in this 40-minute video by Chris Webb, expert on all things SSAS. You will learn how to create an SSDT-BI project, set its properties, connect to relational data such as a data warehouse, and how to create Data Source Views (DSVs).

Association Rules is one of the most useful and well understood predictive analytical techniques, and a fundamental algorithm of classical data mining. Let Rafal teach you how to use it in SQL Server in this hands-on, detailed, 1-hour 40-minute, demo-filled video. You will learn about preparing data for analysis, making it efficient, use of the visualisers, including the dependency network viewer, and, most importantly, you will learn how to interpret the results. Rafal takes great care to explain the often confused metrics of the strengths of association: Rule Probability and Rule Importance, giving you plenty of examples to make it easier. Finally, no data mining algorithm would be complete without a set of parameters you can control, and all of them, including the very important MINIMUM_SUPPORT and MINIMUM_PROBABILITY are explained in this video.

Chris Webb introduces the subselect in this short 3-min video. It uses an MDX SELECT statement instead of a cube’s name, and it allows you to pre-filter the members on a hierarchy before you run a query.

MDX has a lot of set and member functions that make it easy to create all kinds of advanced common business calculations. Watch this full-length, 1-hour 40-minute, demo-rich video by Chris Webb to learn how to compute important business reporting metrics using MDX. You will learn about: ParallelPeriod(), PeriodsToDate(), YTD(), QTD(), MTD(), Filter(), NonEmpty(), TopCount(), BottomCount(), TopPercent(), BottomPercent(), Head(), Tail(), Generate(), StrToX/XToStr function family.

The CurrentMember function is, probably, the most important function in MDX: understanding how it works is the key to being able to write all kinds of really useful calculations in MDX, such as time intelligence calculations. Let Chris Webb, noted authority on MDX, explain this function to you in this short, free video.

Being able to think in sets is the key to being able to write more advanced queries and calculations in MDX. Chris Webb introduces the commonest MDX functions that return sets in this 40-minute, demo-driven video, including: .MEMBERS, CROSSJOIN(), .PARENT, DESCENDANTS( ), ANCESTOR(), .NEXTMEMBER, .PREVMEMBER, .LEAD() and .LAG(). You will need these functions for rudimentary navigation using MDX.

Calculated members are the most commonly used way of performing calculations in MDX. Let Chris Webb explain the different ways in which they can be created in this 45-minute video, part of our online MDX course.

Carmel Gunn is a Data Management Specialist from Prodata. Let her show you Excel as an enterprise-grade BI client in this short video, in which she builds a fully fledged enterprise dashboard—in under 30 minutes!—and then shares it with others, even on mobile devices, such as an iPad, iPhone, Android, or a Surface.

Microsoft Clustering is a workhorse algorithm of data mining. It is used to explore data, segment and categorise it, and to detect outliers (or exceptions, anomalies). Each cluster represents naturally occuring groupings of your data points, grouped by their similarities, as described by their various attributes. In this in-depth, 1-hour 50-minute video, Rafal explains clustering concepts, the entire process, and all of the algorithm parameters. The detailed 12-part demo, which forms the heart of this tutorial, shows you the iterative process of clustering, explaining how to segment your own data, such as customers, or products.

A decision tree is a tree of nodes. Each node represents an input value that makes the most profound difference to an output that you wish to study. This free 10-minute video by Rafal introduces this powerful analytical tool, and explains the concepts while analysing simple retail data in a demo.

MDX, or Multidimensional Expressions, has the reputation of being a difficult language, but this reputation is undeserved, even if the concepts it uses are different from those of relational databases. In this free, 29-minute video, part of our series on MDX, world-renowned SQL Server Analysis Services expert Chris Webb introduces three basic concepts: the use of unique names to refer to objects, tuples and how they are used to return values from a cube, and sets, which are ordered lists. Understanding these ideas will give you the theoretical foundation you need to write your own MDX calculations, and queries, for SSAS and PowerPivot.

This 1-hour-20-minute video discusses the entire lifecycle of a Data Mining Model. You will learn how to build models and mining structures, starting by creating a Data Source and Data Source View, how to train it with your data, and how to view the results. Most importantly, you will also understand how to verify a model’s validity, by applying tests of accuracy, reliability, and usefulness. You will understand, and you will also see being used, such key verification techniques as: a Lift Chart, Profit Chart, Classification Matrix, and Cross Validation. Finally, you will see how to predict unknown outcomes using your model. Not only will you hear in-depth explanations, but you will also see 11 live demos, showing you all the aspects of working with Data Mining Models, including using SQL Server Data Tools (SSDT), and Microsoft Excel, for predicting (scoring) sales to future, potential customers, based on their demographic characteristics, and their shopping habits, just discovered using a Decision Tree, and a simple mining model.