Similar authors to follow

About Marco Russo

Marco Russo is a consultant and trainer in Business Intelligence.He is a founder of SQLBI (www.sqlbi.com), which provides mentoring and consulting on Business Intelligence based on Microsoft technologies. He has particular competence and experience in sectors like financial services, manufacturing, gambling, and commercial distribution.Marco has several Microsoft certifications including SSAS Maestro, MCDBA, MCSD, MCSA and MCAD, and is also an MVP and a former MCT. In addition to his BI-related publications, he has authored books about .NET programming. He is also a speaker at international conferences such as Microsoft Ignite, TechEd, PASS Summit, and SQLBits.

Author Updates

I recently implemented Power BI models by extracting data from Azure Data Lake Storage (ADSL) Gen 2, and I wanted to share a lesson learned when using this connector.
In this project, I had tens of thousands of CSV files stored in the data lake and organized in different folders according to the Folder Path.
The user interface of Power Query provides the user with two options: File System View CDM Folder View
The CDM Folder View is not useful for plain CSV files. By pr

This article explains how to show different customers with the same name in a Power BI report by using zero-width spaces, thus simplifying the presentation without adding visible characters to make the names unique.
Sometimes, we deal with cases where two or more entities of the same type have the same name. If we take the example of customers, the data model stores both the customer code and the customer name to correctly identify each customer. The customer code is what identifies e

DAX Studio is the best tool to analyze the performances of DAX queries. In this webinar recorded for powerbi.tips, Marco Russo shows how to collect metrics in DAX Studio and investigate on storage engine and formula engine cost of some sample queries.

In this article, we share an idea to reduce the friction in using Power BI, by extending the DAX language with new visual calculations.
At SQLBI, we teach DAX. We have been teaching DAX to thousands of people all over the world. Both in-person and online, we have met an extremely diverse crowd of students: different cultures, ages, backgrounds, experiences, and requirements. If there is a single statement all our students would agree on, it is the following: learning DAX is much harde

Starting from the May 2020 version of Power BI Desktop, regardless of the Windows locale settings DAX always uses standard separators by default. This change does not affect most Power BI users around the world; but if you use the comma as a decimal separator and 10,000 is just ten and not ten thousand, then you are affected by this change.
First of all, you can restore the previous behavior as I describe in this post, but the recommendation and the default are now to use the standard

This article shows how to use the USERELATIONSHIP function in DAX to change the active relationship in a CALCULATE function.
If two tables are linked by more than one relationship, you can decide which relationship to activate by using USERELATIONSHIP. Indeed, you can only have one active relationship between any two tables. For example, you start with a model where Sales is related to Date through the Date column, like in the following picture.
If you create a second relation

This article shows how to use DAX and conditional formatting together to highlight the minimum and maximum values in a matrix in Power BI.
Conditional formatting is a handy feature in Power BI; combined with DAX skills, conditional formatting can produce excellent results. In this example, we show how to highlight relevant information in a matrix by formatting the desired cells.
We look at the sales of Contoso in different countries and years. If we look for geographical areas

This article describes how to optimize a slow Power BI report with a large number of card visuals, by obtaining the same graphical layout with a smaller number of visuals.
Every visual element in a Power BI report must complete a number of tasks to provide its result. Visuals showing data must generate one or more DAX queries to retrieve the required measures by applying the correct filters. The execution of these queries increases the wait for the end user and increases the workload

This article describes how to use conditional formatting with a DAX expression to color the rows of a table in Power BI based on the order number instead of using the alternate rows color formatting option.
Power BI offers the capability to show tables with alternating colored rows so to make it easier to read the content of a report.
You might want to use a similar alternate coloring style for a different purpose: highlighting all the lines of one same order. If you look care

VertiPaq Analyzer is a set of tools and libraries to analyze VertiPaq storage structures for a data model in Power BI and Analysis Services Tabular. In this webinar recorded for powerbi.tips, Marco Russo shows how VertiPaq Analyzer has been integrated into DAX Studio, explaining how to use these features to optimize the size and the performance of a data model.

This article describes in which conditions the precedence of calculation groups might return unexpected results when filtering calculation items in both the visuals and the measures present in a report.
When there are multiple calculation groups in a model, we must define their precedence. This ensures we get the expected result when applying different calculation groups to a measure. More details are available in the Understanding Calculation Group Precedence article.
Even in

This article explains how to control the result provided by a formula in a DAX measure when the meas-ure is being used in a report with granularity below the granularity supported by the formula.
In the article Obtaining accurate totals in DAX, we described the granularity of a formula as being the level of detail that the formula uses to compute its result. If you are not familiar with formula granularity, please read that article first, because this is the natural continuation.

This article describes how to compute visual totals accurately, in case a measure does not provide the right result at the total level but works correctly in other rows of the report.
In simple DAX measures, the total of a report is the sum of its individual rows. For more sophisticated measures, the total might seem wrong because the formula does not include an aggregation over the rows that are visible in the report. For example, if the total of a measure must be the sum of the valu

This article describes how to control format strings in calculation groups. Before starting, we suggest you read the previous articles in this series.
Each calculation item can change the result of a measure through the DAX code in the Expression property. The author of a calculation item can also control the display format of the result, overwriting the standard behavior of the measure where the calculation item is applied. The Format String Expression property can contain a DAX expr

UPDATE 2020-03-28: Calculation groups are coming soon to Power BI Desktop, read the added section at the bottom of the post.
I am glad to announce that we just released an additional module about Calculation Groups to the Mastering DAX video course. All current students of the video course will immediately be getting access to this new section.
We covered calculation groups in The Definitive Guide to DAX book published last year. Yet we waited until the release of the feature

COALESCE is a DAX function introduced in March 2020. This article describes the purpose of COALESCE and how to simplify DAX expressions by removing verbose conditions, and yet obtain the same result.
A common requirement in authoring DAX code is to provide a default value for the result of a calculation that might otherwise be blank. The classic approach to solving this requirement is by using an IF statement. For example, the following code returns the value of either the Sales[Net P

This article describes projection functions and techniques in DAX, showing the differences between SELECTCOLUMNS, ADDCOLUMNS, and SUMMARIZE.
2020-03-15 UPDATE: The original version of this article published in 2011 did not mention SELECTCOLUMNS, which was introduced in 2015. This article was rewritten in 2020 to provide updated coverage of projection functions in DAX.
The projection is provided by this classic SELECT in SQL: SELECT * FROM Product
It corresponds to th

This article describes how to implement currency conversion for reporting purposes in Power BI.
Currency conversion applied to reporting can include many different scenarios. Data in multiple currencies, report with a single currency Data in multiple currencies, report with multiple currencies Data in a single currency, report with multiple currencies
The rule of thumb is to apply the currency exchange conversion upfront. Therefore, it is a good idea to solve all the scenarios

This article describes the types of relationships available in Power BI and Analysis Services, clarifying the differences in cardinality and filter propagation of physical relationships.
Power BI and Analysis Services rely on a semantic model based on the Tabular Object Model (TOM). Even though there are different tools to manipulate these models – we suggest using Tabular Editor – the underlying concepts are identical because the engine and the data model is the same: we call it the

This article explains the behavior of LASTNONBLANK, LASTNONBLANKVALUE, and similar DAX functions, also providing patterns for performance optimization.
The DAX language can operate on aggregated values and on individual rows of tables in the data model, but it does not offer an immediate syntax to perform visual-level calculations over the cells visible in a report. The reason is that a DAX measure must work in any condition and in any report, which is why the notion of row context an

DAX Formatter was created in 2013 to help users format their code based on the Rules for DAX Code Formatting published in October 2012. Because the main goal is to make the code immediately readable and recognizable, DAX Formatter purposely does not provide many options to customize the format. But we are now considering a small change.
Over the years, we only added three options: Separators: the choice between the comma (,) and semicolon (;) separators is automatic, but you can o

This article describes how to write efficient DAX expressions that compute the compound interest of incremental investments made throughout the holding period, using the PRODUCTX function. Defining compound interest
If you make an investment and hold it for a period of time, the future value of your investment depends on the rate of return over the investment holding period.
For instance, consider an investment of $100 in a simple debt instrument with an annual fixed interest

This article describes the reasons why an Excel pivot table may be slow when using the Analyze in Excel feature of Power BI.
Power BI uses the Analysis Services Tabular engine to process queries coming from reports. When you create a Power BI report, the query sent to the engine uses the DAX language, which usually produces more optimized query plans. However, the Analysis Services engine is also able to understand MDX queries sent to a Tabular model. Excel is a client tool that can c

Another year has gone by and it has now been 10 years since DAX was first introduced in late 2009. As usual, it is time to review the past year and to take a look at the year ahead. New DAX functions and syntax in 2019
Microsoft released 13 new functions in 2019. The first 4 functions are related to the calculation group feature, which is now only available on Azure Analysis Services and Analysis Services 2019: ISSELECTEDMEASURE: Returns true if one of the specified measures is cu

This article describes how to implement a syntax equivalent to the T-SQL function NULLIF and the ANSI SQL function COALESCE, in DAX.
As do many languages, DAX enables people to get the same result through different techniques. However, the same semantic may produce different query plans, so it is a good idea to know different techniques because this could be useful when you want to optimize the performance of a slower expression.
UPDATE 2020-02-27 : the COALESCE function is av

This article describes how you can create a comparison with the previous time period in a visualization, regardless of whether the time periods are consecutive or not.
Time intelligence calculations in DAX are usually created considering consecutive periods in any type of comparisons. This approach might not work well when the requirement is to compare the differences between a selection of non-consecutive periods. In that case, the “previous” element in a visualization might not corr

If you created a Tabular model using one of the preview versions (CTP, RC, …) of Analysis Services 2019, you don’t have the final structure for the Ordinal attribute that Microsoft defined in the final version of compatibility level 1500.
A calculation group is like a table in the Tabular model and a calculation item is a value in a column of the table defined for the calculation group. In order to sort the calculation item there is an additional column called Ordinal used to manage t

This article describes the differences between eager evaluation and strict evaluation in DAX, empowering you to choose the best evaluation type for your data models.
UPDATE 2020-01-31: The new IF.EAGER function can enforce the eager evaluation described in this article without having to refactor the DAX expression using variables. The content of this article is still valid, just consider IF.EAGER instead of IF in those cases where the eager evaluation is more convenient for your formu

This article shows a basic technique used to sort months according to a fiscal calendar, by using a couple of calculated columns and the “sort by column” feature of Power BI.

By default, Power BI sorts columns alphabetically. So when it applies to months, the default alphabetical sorting order of months in a new Date table is incorrect.
The Sort by Column feature of Power BI can sort the months by name, relying on the month number which needs to contain numbers from 1 to

This article describes how blank values considered in a DAX conditional expression can affect its query plan and how to apply possible optimizations to improve performance in these cases.
An important metric to consider in optimizing DAX is the cardinality of the data structures iterated by the formula engine. Sometimes the formula engine needs to scan huge datacaches because it cannot leverage the auto-exist logic of DAX. Optimizing these scenarios requires a deep understanding of th

Now expanded and updated with modern best practices, this is the most complete guide to Microsoft’s DAX language for business intelligence, data modeling, and analytics. Expert Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization. You’ll learn exactly what happens under the hood when you run a DAX expression, and use this knowledge to write fast, robust code. This edition focuses on examples you can build and run with the free Power BI Desktop, and helps you make the most of the powerful syntax of variables (VAR) in Power BI, Excel, or Analysis Services. Want to leverage all of DAX’s remarkable capabilities? This no-compromise “deep dive” is exactly what you need.

Perform powerful data analysis with DAX for Power BI, SQL Server, and Excel

Renowned DAX experts Alberto Ferrari and Marco Russo teach you how to design data models for maximum efficiency and effectiveness.

How can you use Excel and Power BI to gain real insights into your information? As you examine your data, how do you write a formula that provides the numbers you need? The answers to both of these questions lie with the data model. This book introduces the basic techniques for shaping data models in Excel and Power BI. It’s meant for readers who are new to data modeling as well as for experienced data modelers looking for tips from the experts. If you want to use Power BI or Excel to analyze data, the many real-world examples in this book will help you look at your reports in a different way–like experienced data modelers do. As you’ll soon see, with the right data model, the correct answer is always a simple one!

By reading this book, you will:

• Gain an understanding of the basics of data modeling, including tables, relationships, and keys

A pattern is a general reusable solution to a commonly occurring problem. This book is a collection of ready-to-use data models and formulas in DAX, that you can use in Power Pivot for Excel and in Microsoft Analysis Services Tabular. Create your data model faster by using a DAX pattern!

Create a semantic model and analyze data using the tabular model in SQL Server 2016 Analysis Services to create corporate-level business intelligence (BI) solutions. Led by two BI experts, you will learn how to build, deploy, and query a tabular model by following detailed examples and best practices. This hands-on book shows you how to use the tabular model’s in-memory database to perform rapid analytics—whether you are new to Analysis Services or already familiar with its multidimensional model.

Discover how to:

• Determine when a tabular or multidimensional model is right for your project

• For BI professionals who are new to SQL Server 2016 Analysis Services or already familiar with previous versions of the product, and who want the best reference for creating and maintaining tabular models.

Transform your skills, data, and business—and create your own BI solutions using software you already know and love: Microsoft Excel. Two business intelligence (BI) experts take you inside PowerPivot functionality for Excel 2013, with a focus on real world scenarios, problem-solving, and data modeling. You'll learn how to quickly turn mass quantities of data into meaningful information and on-the-job results—no programming required!

Understand the differences between PowerPivot for Self Service BI and SQL Server Analysis Services for Corporate BI

Authors’ note on using Microsoft Excel 2016: This book’s content was written against Excel 2013, but it is useful and valid for users of Excel 2016 too. Excel 2016 introduces several new DAX functions and an improved editor for DAX without changing any existing behavior. In other words, all of the concepts and examples explained in this book continue to work with Excel 2016.

About This Book

Add calculations to the cube, including implementing currency conversion and a date tool dimension

Use security to control access to data in your cube

Who This Book Is For

If you are an Analysis Services cube designer wishing to learn more advanced topic and best practices for cube design, this book is for you.You are expected to have some prior experience with Analysis Services cube development.

In Detail

Microsoft's SQL Server Analysis Services 2012 Multidimensional is an OLAP server that allows end users to analyze large amounts of business data quickly and easily, using a variety of tools including Excel, and to create their own reports without IT involvement. It is part of the Microsoft SQL Server suite of tools and has been used widely on Business Intelligence projects for the last 15 years; it is now the most commonly used OLAP server in the world.

Expert Cube Development with SSAS Multidimensional Models will teach you tips and tricks for cube design from some of the most experienced Analysis Services experts in the world. Topics covered include designing dimensions and cubes, implementing common MDX calculations, security and performance tuning.

The book takes you through the whole lifecycle of Analysis Services cube development, from how data warehouse design affects your cube, through cube and dimension design to performance tuning and monitoring. Some previous experience of Analysis Services is assumed– the focus is on best practices and design patterns.

The book starts off with a discussion of data warehouse design and how it relates to Analysis Services. Following on from that, best practices relating to Analysis Services cube and dimension design are covered, including topics such as optimizing dimension attribute relationships and handling fact tables that contain data at different granularities.

Next, the book looks at using MDX to implement common business calculations, and then moves on to more advanced problems like currency conversion calculations and creating a date tool dimensions.

With LINQ, you can query data from a variety of sources -- including databases, objects, and XML files -- directly from Microsoft Visual Basic or C#. Guided by data-access experts who've worked in depth with LINQ and the Microsoft development teams, you'll learn how .NET Framework 4 implements LINQ, and how to exploit it. Clear examples show you how to deliver your own data-access solutions faster and with leaner code.

Analyze tabular data using the BI Semantic Model (BISM) in Microsoft SQL Server 2012 Analysis Services—and discover a simpler method for creating corporate-level BI solutions. Led by three BI experts, you’ll learn how to build, deploy, and query a BISM tabular model with step-by-step guides, examples, and best practices. This hands-on book shows you how the tabular model’s in-memory database enables you to perform rapid analytics—whether you’re a professional BI developer new to Analysis Services or familiar with its multidimensional model.

Discover how to:

Determine when a tabular or multidimensional model is right for your project

Microsoft PowerPivot for Excel 2010: Give Your Data Meaning introduces PowerPivot in Excel 2010 to power users and data analysts who want to give their data meaning by creating their own Business Intelligence models. And with Microsoft Excel 2010: Data Analysis and Business Modeling, you'll learn the best ways to use Office Excel 2010 for data analysis and business modeling. Award-winning professor and statistician Wayne Winston shares practical examples to help you transform data into bottom-line results. Web site includes practice files.

This is a practical tutorial for Analysis Services that shows readers how to solve problems commonly encountered while designing cubes, and explains which features of Analysis Services work well and which should be avoided. The book walks through the whole cube development lifecycle, from building dimensions, cubes and calculations to tuning and moving the cube into production. This book is aimed at Analysis Services developers who already have some experience but who want to go into more detail on advanced topics, and who want to learn best practices for cube design.