Project Botticelli

Status message

Video playback issues? See tips for supported browsers. Clearing cookies and cache often helps.

Most Popular

Presentations (PPTs)

Over 50 of the popular PPTs (not PDFs) on: Artificial Intelligence, Azure ML, Azure AI (incl Cognitive Services and Bots), Data Science, Power BI, Microsoft R and ML Servers, R in general, and advanced analytics. Includes Microsoft Ignite, Data Science Summit and the Advanced Analytics and Data Science Roadshows, as well as various keynotes and roadshows focused on the future of data and databases from global conferences from this and the past years are available here.

DAX: Calculated Columns vs. Measures

DAX Formula for Classifying Data Using a Calculated Column

Learn the difference between calculated columns and measures in Data Analysis Expression (DAX) in this free, 10-minute video by Marco Russo, a well-known book author on the subject. Calculated columns are calculated row-by-row when the content of a table is refreshed, whereas measures are computed at query time, by aggregating rows. However, they have different uses, including filtering or classifying data, so you often need to make a trade-off between purpose and performance.

DAX in Action!

DAX Formula for Counting New Customers Using CALCULATE, FILTER, and COUNTROWS

See the power of Data Analysis Expression (DAX) language in BI Semantic Model (BISM) with PowerPivot and SQL Server Data Tools (SSDT) in this free, 20-minute video by Alberto Ferrari. In the demo shown in this video we create a simple data model, based on the AdventureWorks sample database. We show you how to write a challenging formula: counting how many new and returning customers make a purchase every month. If you would like to gain DAX skills, this video, first one in our series focusing on DAX, shows you what could be accomplished without even having to change the model of your data.

DAX: The CALCULATE Function

DAX: CALCULATE Function with ALL and FILTER

CALCULATE is the most important function in Data Analysis Expression (DAX) because it allows you to manipulate filter context, which is necessary for building real-world calculations. Let Marco Russo explain it to you in this 47-minute video tutorial that contains 5 detailed demos. You will learn: when to use CALCULATE, its syntax, how to manipulate filters at column granularity level, and how to transform a row context into a filter context. Learning these concepts will enable you to write advanced calculations in DAX, and it will make following the remaining modules of our DAX video series easier.

Microsoft SQL Server 2012 Business Intelligence (Article)

SQL Server 2012 Logo

This article, by Rafal, discusses pivotal BI aspects of Microsoft SQL Server 2012: BI Semantic Model (BISM), PowerPivot 2, Data Analysis Expressions (DAX), the future of OLAP Cubes and MDX (Multidimensional Expressions), and, briefly, it touches on the role of the Cloud for analytics.

Querying with DAX

Querying with Data Analysis Expressions using DAX Studio by Marco Russo

Data Analysis Expressions (DAX) is useful for querying data, in addition to its more common use for defining measures and calculated columns in PowerPivot or tabular models. Marco Russo explains the tools, including DAX Studio, and the syntax necessary for writing DAX queries in this 46-minute video, containing 7 detailed demos, part of our series on DAX. You will learn about the EVALUATE statement, how to control projection of data by using ADDCOLUMNS and SUMMARIZE functions, how to use the ROW function to test new measures for your data model, and how to use DAX measures within MDX queries. By learning these concepts you will be able to use DAX queries as a data source for your reports, and for Excel tables.

Introduction to DAX in Excel

Using Aggregation Iterators (SUMX) to Calculate Gross Profit Margins

Introduce yourself to Data Analysis Expression (DAX) by learning the syntax and the fundamental functions of this language in this 40-minute video by Marco Russo and Alberto Ferrari, world-known SSAS experts. DAX is the language used to define calculation expressions in PowerPivot. It works in Excel 2010, and it is in the core of Excel 2013. DAX is also the programming language for Microsoft SQL Server Analysis Services Tabular Models. It has a simple syntax that will be instantly familiar to Excel users, because it replicates Excel syntax wherever possible. Nevertheless, DAX also introduces many new functions, in order to express tabular and columnar concepts, which are not part of Excel workbooks—Excel is focused on cells and ranges, while DAX focuses on entire columns and tables. By watching this video, which includes 9 demos, you will become familiar with the basic syntax of DAX, and you will be ready to write your first calculations.

Books about BI

This article lists books about BI, Statistics, Data Warehousing and Management, which we recommend in conjunction with our courses and seminars.

DAX Evaluation Context

FILTER and ALL DAX Function for Context Manipulation

Evaluation Context is fundamental to understanding the behavior of DAX. Marco Russo explains: row and filter contexts, their propagation through relationships, and manipulation using: FILTER, ALL, EARLIER, VALUES, RELATED, and RELATEDTABLE functions in this 50-minute video that contains 8 detailed demos. Learning these concepts will enable you to write correctly functioning formulas, and it will prepare you to fully understand CALCULATE, the most important function in Data Analysis Expressions, as well as the remaining modules of our DAX video series.

Introduction to PowerPivot for SQL Server 2012

The second module in this online training course, offers a 1 hour introduction to PowerPivot for SQL Server 2012, sometimes referred to as PowerPivot “2”. This module is of importance to those who need to model tabular data for analytics, or who simply want to explore a data set, before making a decision if it is worthwhile analysing it further. Also, if you plan on learning about BI Semantic Model, you need to know about PowerPivot “2”, as Tabular Mode of SSAS, and BI Semantic Model, make extensive use of the new PowerPivot features, extending them to enterprise needs. This module is presented as a series of slides, graphics, discussions, and 15 demos, recorded in high-resolution, so that you can follow the steps yourself.

How to Calculate with Hierarchies in DAX?

Marco shows an example of the use of hierarchies, to calculate Ratio-to-total, in DAX

Learn how to calculate hierarchical ratio-to-parent measures in DAX, and how to use them in pivot tables, with Marco Russo, BI expert from sqlbi.com.

DAX Time Intelligence

Time Intelligence Semi-Additive Measures in DAX

Time Intelligence is a set of techniques for performing time and date-based calculations. It covers a broad set of calculations needed for business reporting. Let Alberto Ferrari, book author and a renowned Data Analysis Expressions expert, show you an in-depth tour of these functions, in this 1-hour video that contains 6 detailed, hi-def demos. You will learn about: calendar tables, time-based role dimensions, TOTALYTD, TOTALQTD, TOTALMTD, and the CALCULATE function used with time-oriented, predefined and custom filters, and dealing with semi-additive measures, including last-day, or last-non-empty aggregations.

What is Time Intelligence?

TOTALYTD DAX Time Intelligence Function

Time Intelligence means doing calculations over periods of time or dates. Let Alberto Ferrari show you how to use TOTALYTD to compute Total Sales Year-to-Date in Excel in this 2-min video.

Many-to-many Relationships in DAX

Alberto shows DAX formula for handling a many-to-many relationship

Learn how to use many-to-many relationships in DAX by applying formulas which correctly handle filter context propagation over relationships in this short, 3-minute video by Alberto Ferrari.

Hierarchies in DAX

Marco Russo explains Parent/Child Hierarchies in DAX

Hierarchies, including parent-child ones, can be created using a combination of a few DAX formulas and a data model in Power Pivot or SSAS Tabular. Marco Russo, BI expert from sqlbi.com, shows you how to work with them in this 55-minute video. Well-implemented hierarchies can greatly enhance navigation and usability, so learn how to implement their calculation logic by watching this demo-rich tutorial.

Self-Service Data Mash-ups with Microsoft PowerPivot and SharePoint Server

Creating a Relationship Using Microsoft PowerPivot

This video shows data being mashed-up from multiple sources using Microsoft PowerPivot and DAX, all by the end user, in a self-service way.

DAX Patterns: Banding, New vs Old, Many-to-many

Alberto Ferrari Explains Many-to-many DAX Pattern

Alberto Ferrari, BI expert from sqlbi.com shows how to solve common business patterns using the DAX language in this 50-minute, demo-focused video. Patterns are useful for two reasons: they let you learn advanced DAX techniques and, by adapting their code to your needs, you can use them as a quick recipe for your own scenarios. This video discusses banding, computing new vs old metrics, and many-to-many relationship patterns.

DAX AW Sample Data Set

If you would like to follow Marco and Alberto’s demos, you will find it easier if you use the same data as they do in the video. This workbook already contains calculated columns and measures created in the video. You can create new ones or remove the existing ones and creating the same ones as you have seen in the video. We offer this workbook as a free bonus for Full Access Members who are following our DAX online course.

Introduction to DAX

This 1-hour video introduces you to the language of DAX: Data Analysis Expressions. Marco Russo, and Alberto Ferrari, world-experts on this subject, and popular book authors, also explain how to get ready for your study, what to prepare, and what to expect in the end.

Working with Iterators

Many DAX functions, including all the aggregations, iterate a table executing a DAX expression for each row, in a specific row context. 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. In this module, you will see different examples that explains how to control the granularity of an iterator, so that you can obtain the expected result.

Querying with DAX

You can use DAX to create calculated columns and measures, but you can also use DAX as a query language with the help of specific tools such as DAX Studio. The query syntax is based on the EVALUATE statement, which materializes to the client the result of a table expression. For this reason, after learning how to write DAX queries using EVALUATE in DAX Studio, you will learn a number of table functions useful in both queries and other DAX expressions.

Table Functions

The learning goal of this module is to introduce you to the notion of table functions and to quickly describe the most important ones, but not to provide a detailed explanation of all the functions yet, as that happens in the later modules. Above all, you will see the interaction between tables and relationships and how to create calculated tables.

DAX and the Data Model

DAX is the query and calculation language of the Power BI Data Model. DAX can be used to create three types of object in Power BI Desktop: calculated columns, which add new columns containing derived values to an existing table; measures, which control how data aggregates up in reports; and calculated tables, which are tables derived from other tables loaded into the Data Model.

CALCULATE Function

CALCULATE is by far the most important, useful, and complex function of the DAX language. In reality, the function itself is an easy one. 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 a full chapter absolutely necessary.

Evaluation Contexts

At this point in the course, you have learned the basics of the DAX language. You know how to create calculated columns and measures, and you have a good understanding of common functions used in DAX. With the knowledge you have gained so far, you can already create many interesting reports, but you will need to learn evaluation contexts in order to create reports that are more complex.

Time Intelligence in DAX

In this section, you will learn how to implement common date-related calculations such as year-to-date, year-over-year, comparisons over years, running total, and moving annual total.

Evaluation Contexts and Relationships

After you have seen the basics of evaluation context and the CALCULATE function, it is time to analyze how the relationships of a data model affects their behavior. You will learn how to transfer a row context following relationships, and how the filter context automatically propagates through relationships.

Advanced Relationships

In this final section, you will learn how to handle complex relationships between tables by leveraging the DAX language. The goal is not to just give you pre-built patterns that you can use in your model. Instead, we want to show you unusual ways of using DAX to build complex models, to widen your idea of relationships, and to let you experience what you can achieve with DAX formulas.

DAX and the Data Model

DAX and the Data Model

DAX is the query and calculation language of the Power BI Data Model. This video introduces its fundamental concepts: calculated columns, measures, and calculated tables.

Hierarchies in DAX

In this section, you will learn how to create calculations over hierarchies and how to use DAX to transform a parent-child hierarchy into a regular hierarchy managed by the data model, hiding levels that should not be visible in a ragged hierarchy.

Building a Date Table

Almost all data models need to analyze data over time. The first step for handling date calculation is to create a Date table, which simplifies any date-related calculation. Because of its importance, you should be careful when you create a Date table.

DAX and the Data Model (2018 Updates)

DAX and the Data Model (2018 Updates)

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.

Advanced Filter Context

In this section we are going to uncover all the complexities of evaluation contexts and show many examples of expressions that look wrong at first sight, but only because you do not have a full understanding of how they work. Learning these detailed topics is mandatory if you want to fully understand DAX and learn how to use it in the more complicated scenarios.

Online Courses