The dynamic version of the ABC Classification pattern is an extension of the Dynamic Segm…
This article describes the semantic difference between ALLEXCEPT and the joint use of ALL and VALUES, showing practical examples of the different results in Power BI and SSAS 2016.
This article describes how to add a measure to a Tabular model already deployed on Analysis Services by using C# or PowerShell. This can be useful to deploy user-defined metrics without deploying the entire database.
This article describes a few query limits existing in DirectQuery for both Power BI and Analysis Services Tabular 2016, explaining when you can modify them.
This article shows how variables in DAX can impact the coding style, simplifying a step-by-step approach and improving the readability of your code.
Translations are a new feature in Analysis Services Tabular 2016. This article explains how to use this feature and introduce SSAS Tabular Translator, a free tool to edit translations files.
DAX calculations can leverage relationships present in the data model, but you can obtain the same result without physical relationships, applying equivalent filters using specific DAX patterns. This article show a more efficient technique to apply virtual relationships in DAX expressions.
Writing measures referencing other measures is in general a good idea that simplifies the DAX code, but you might face specific bottlenecks. This article describes which performance issues might arise when different measures aggregate the same column using different filter arguments, and how to improve efficiency of your DAX code.
The new DAX available in Excel 2016 and the data model in Power BI and Analysis Services 2016 offer tools to manage many-to-many relationships in a more efficient way than previous version, as described in this article.
In this article, Alberto Ferrari describes a new efficient way to compute returning customers in DAX thanks to an idea suggested by a student attending an Optimizing DAX workshop.
A data model for DAX has three numeric data types: integer, floating point, and fixed decimal number. This article describes them and explains why the fixed decimal number should be used instead of the floating point in most scenarios.
When you create a data model in Power BI, you should consider how to properly use naming convention and what columns to include, in order to improve usability and performance. This article provides a quick list of best practices valid for both Power BI and Power Pivot.
A filter argument in CALCULATE is always an iterator. Finding the right granularity for it is important to control the result and the performance. This article describes the options available to create complex filters in DAX.
In Power BI Desktop (as of February 2016) you have to use DAX to apply calculations over dates (such as year-to-date, year-over-year, and others), but you do not have the Mark as Date Table feature. This article describes which scenarios are impacted and the possible workarounds.
The infographic “Space Report” submitted to the Best Report Contest in Power BI is not only a collection of nice charts and graphics. There are several little gems implemented to overcome certain limitations, as you will read in this article.
DAX introduced a GROUPBY function that should replace SUMMARIZE in some scenarios. This article describes how to use GROUPBY in nested grouping scenarios and other improvements.
You can create static tables in DAX using the DATATABLE function. This article describes the syntax of this new feature and shows when and how to use it.
Applying security roles to a SSAS Tabular database might impact query performance. This article describes existing issues and possible workarounds. This is an advanced article, a good knowledge of storage engine and formula engine is required.
The blank value in DAX is a special value requiring particular attention in comparisons. It is not like the special null value in SQL, and it could appear in any conversion from a table expression. This article explores in details the behavior of the blank value in DAX, highlighting a common error in DAX expressions using SWITCH.
When you apply a multiple selection to a slicer or to a filter, you obtain a logical OR condition between selected items. This article shows how to implement a logical AND condition in a measure instead of the standard OR one.
Hardware and virtualization settings have a big impact on Analysis Services Tabular performance. This article describes best practices for the most important settings to check.
In the 2015 September update, Power BI introduced calculated tables, which are computed using DAX expressions instead of being loaded from a data source. This article shows the usage of calculated tables to solve the pattern of transition matrix for customer categorization.
The context transition in DAX is the transformation of row contexts into an equivalent filter context performed by CALCULATE and CALCULATETABLE. Managing this behavior is the next step in learning DAX once you understand row context and filter context. This article provides the basics of context transition.
Understanding the difference between row context and filter context is important to use DAX correctly. This article introduces you to these two concepts.
One of the first concepts to learn in DAX is the difference between calculated columns and measures. This article shortly recap the differences and describes when to use each one.
The relationships in a data model queried by DAX are a fundamental part of the engine and simplify the query itself. This article examines the cost of relationships in a data model, providing hints to optimize them.
ALLSELECTED looks like a special function that is able to understand what the user selected in the pivot table, retrieving the original filter context under which the pivot table is running. However, how it is possible that a DAX function knows what a user selected in a pivot table? If the source of a report is not a pivot table but a DAX query, is ALLSELECTED still going to work? This article answer to these questions providing a detailed explanation of ALLSELECTED behavior.
Analyzing table and column size is an important step in optimizing a data model for Power Pivot, Power BI, or Analysis Services Tabular. This article describes VertiPaq Analyzer, an Excel workbook to analyze detailed information extracted from Dynamic Management Views.
DAX is a functional language that does not allow you to create functions. However, if you need to pass a parameter to an algorithm, you can use a measure passing a value through a filter argument in CALCULATE, as described in the following article.
Choosing the right hardware is critical for a solution based on Analysis Services Tabular. Spending more does not always mean having a better machine. This short article describes how to choose the right server and, as you will see, the perfect Tabular server is not expensive.
The default memory settings for Analysis Services Tabular might be less than optimal. This article describes why this might happen, and guides you choosing the right configuration.
In this article, you learn a new feature in DAX 2015: variables. The 2015 version of the DAX language has many new functions, but none of them is a game changer for the language as variables are.
In SQL there are different types of JOIN, available for different goals. This article shows what are the equivalent syntaxes supported in DAX.
The SQL functions IN and EXISTS are useful to implement tests over a set of values. This article describes the corresponding syntax in DAX language.
You might have used FIRSTNONBLANK and LASTNONBLANK in semi-additive measures, but you might not be aware that their use is not limited to time intelligence functions. This article shows alternative scenarios where these functions are useful.
This article describes how to implement a custom year-over-year calculation in DAX based on arbitrary associations between different periods. As an example, we describe the comparison of the 53rd week in a ISO Calendar.
The names used for table, columns, and measures in DAX cannot be renamed without breaking existing DAX formulas. This article describes how to leverage translations to decouple user interface from internal model names in Analysis Services Tabular.
This page contains descriptions and links to necessary tools and resources for DAX and Tabular developers.
SUMMARIZE is a function that looks quite simple, but its functionality hides some secrets that might surprise even seasoned DAX coders. In this article, we analyze the behavior of SUMMARIZE, in order to completely describe its semantic. The final advice might surprise you: we will suggest to avoid the use of SUMMARIZE in your code, unless you know exactly which of the functionalities of SUMMARIZE are safe to use.
The autoexist feature used by MDX queries sent to an Analysis Services or Power Pivot data models can have different behaviors depending on the normalization level. This article describes these differences and provides best practices to avoid unwanted results.
The GROUP BY condition of a SQL statement is natively implemented by SUMMARIZE in DAX. This article shows how to use SUMMARIZE and an alternative syntax to group data.
The WHERE condition of a SQL statement has two alternatives in DAX: FILTER and CALCULATETABLE. In this article we explore the differences between them, providing a few best practices in their use.
The DIVIDE function in DAX is usually faster to avoid division-by-zero errors than the simple division operator. However, there are exceptions to this rule, described in this article through a simple performance analysis.
Computing the rolling 12-month average in DAX looks like a simple task, but it hides some complexity. This article explains how to write the best formula avoiding common pitfalls using time intelligence functions.
The new forecasting capabilities in Power View for Office 365 has certain requirements for the column used in x-axis. This article provides a guide to make your data model compatible with the new forecasting feature.
In DAX there are different ways to test whether a table is empty. This test can be used in complex DAX expressions and this short article briefly discuss what are the suggested approaches from a performance perspective.
A common best practice is to use CALCULATETABLE instead of FILTER for performance reason. This article explore the reasons of that and explain when FILTER might be better than CALCULATETABLE.
How do you get the last process date for an SSAS Tabular Database? This article shows the techniques you can use and explains how to interpret the information available.
Creating a hierarchy in a Power Pivot or Tabular data model is very easy, but you might experience performance issues if the hierarchy is not “natural”. This article explains how to create hierarchies avoiding these problems.
Values such as inventory and balance account, usually calculated from a snapshot table, require the use of semi-additive measures. In Multidimensional you have specific aggregation types, like LastChild and LastNonEmpty. In PowerPivot and Tabular you use DAX, which is flexible enough to implement any calculation, as described in this article.
In a Power Pivot or Tabular model that has inactive relationships, you can use the USERELATIONSHIP function to apply an inactive relationship to a particular DAX expression. Its usage is simple in a measure, but you might consider alternative syntax in a calculated columns, as it is explained in this article.
In the traditional star schema design of a Data Mart, you replace a missing, unknown or wrong date in the fact table with a dummy value in the Date dimension table. In Tabular, handling a Date table requires an existing date and you cannot use a NULL date in a Date table. This article describes how to apply the NULL replacement in the fact table using views, without altering the relational structure of the Data Mart.
With DAX you can calculate the sales of the first, second and third year of a new customer without any ETL. In this article you see how to implement this calculation with good performance.
You can implement the calculation of stock inventory over time in several ways in DAX. The xVelocity technology in Power Pivot and Analysis Services Tabular is so fast that you might considerboth dynamic calculation and snapshot based calculation
When you create a Tabular model in Microsoft SQL Server Analysis Services (SSAS), you have to define all of the measures that the user will be able to use. All the measures defined in the measure grid of a table are displayed within a measure group having the same name of the table itself.
Grouping transactions by customers’ age requires a computation made row by row at transaction level. DAX offers an elegant solution using calculated columns, which is described in this article.
This article contains a short checklist of what you have to do in order to optimize the memory used by a data model in PowerPivot or in Analysis Services Tabular, including links to tools and resources that can help you in this task.
The DAX language provides several Time Intelligence functions that simplify writing calculations such as year-to-date (YTD), year-over-year (YOY) and so on. However, if you have special calendar structure such as 4-4-5 weeks’ calendar, you need to write your custom time intelligence calculation. In this article, you will learn how to write the required DAX formulas.
When you use Excel to connect to a Tabular database in Analysis Services, you get a PivotTable as a result. In this article, you learn how to import data in a table writing a DAX query than runs on Analysis Services.
In PowerPivot for Excel 2010 it is possible to import an Excel table in a PowerPivot data model by using the Linked Table feature. Excel 2013 expands this capability by
If you have a PowerPivot workbook created with Excel 2010, in order to navigate and/or update the data model with Excel 2013 you need to upgrade the workbook.
With the modern editors that have automatic code formatting features, each one has its own code formatting style. Usually there are a few “common rules” for code formatting depending on the language, but there was nothing for DAX
Several Time Intelligence functions in DAX use a <dates> argument, which has a special behavior because it can be expressed by using both a column reference syntax, a table expression or a Boolean expression.
In this article we see a function to enable parallelism in commands sent to Analysis Services by using the AMO library even when you need to use the change the QueryBinding. This function is useful because the current AMO library (up to SQL Server 2012) cannot correctly generate the required XMLA code if multiple commands using QueryBinding are executed within the same batch.
Everyone using DAX is probably used to SQL query language. Because of the similarities between the Tabular data modeling and the relational data modeling, there is the expectation that you can do the same operations that SQL allows. However, in its current implementation DAX does not permit all the operations that you can do in SQL. A few of the limitations are caused by the lack of equivalent syntax, others depend on a counterintuitive behavior of the xVelocity in-memory engine when extension columns are involved in a query.
PowerPivot and Analysis Services 2012 Tabular do not support many-to-many (M2M) relationship directly in the data model. However, you can obtain the desired result from a many-to-many relationship by writing a DAX expression. For example, consider the classical M2M relationship between bank accounts and customers.
DAX is the new language used by PowerPivot and Analysis Services in Tabular mode and it resembles the syntax of Excel formula and it can be considered a functional language. You do not have iterative statements, but you can run iterative functions like, for example, SUMX and FILTER. The most important functions in DAX are CALCULATE and CALCULATETABLE, and these functions are also the most complex to master, because of the many operations they do.
Hierarchy handling in DAX is not very easy, due to the fact that hierarchies, unlike it was in MDX, are not first-class citizens in the DAX world. While hierarchies can be easily defined in the data model, there are no DAX functions that let you access, for example, the parent of the CurrentMember. Well, to tell the truth, there is no concept of CurrentMember in DAX either.
In Analysis Services 2012 you can process a table in a Tabular model by several ways: you can process the whole table, you can split the table in several partitions and process a single partition, you can merge partitions and you can incrementally process a single partition by using ProcessAdd, which is the topic of this article.
Memory settings in Analysis Services might be an important tuning of a correct setup, both for production and developer machines. Analysis Services 2012 can be installed in different ways and the new Tabular instance has new memory settings that are important to know.
VertiPaq is the internal column-based database engine used by PowerPivot and BISM Tabular models. High cardinality columns might be the more expensive parts of a table. If you cannot remove a high cardinality column from a table, by using the techniques described in this article you might optimize its storage saving up to more than 90% of original space.
PowerPivot and BISM Tabular handle relationships between tables by using a single column that requires to be a unique column in the lookup table. In this article you will see how to handle relationships between tables that have different granularities, which is a typical scenario whenever you have a monthly budget table and a daily level sales table.
If you want to execute a DAX query from your code to a Tabular model, you might think that an OLE DB connection should be the right way to get your data. It works, but if you your code is written in .NET a better solution is using the ADOMD.NET library. In this article you will see why ADOMD.NET is better in this scenario.
When you design a data model for Tabular you should pay attention to a though topic, which is that of circular dependencies in formulas. It is very important to learn how to handle circular dependencies now because in SQL 2012 (and in PowerPivot 2012) there will be a stronger validation of circular dependencies. Some of the checks described in this article have been introduced with the release RC0 of SQL Server 2012 and were not present before.
In DAX string comparison requires you more attention than in SQL, for several reasons: DAX doesn’t offer the same set of features you have in SQL, a few text comparison functions in DAX are only case-sensitive and others only case-insensitive, and performance might have a relevant impact in your query according to the comparison technique you use.
Using DAX as a query language you will lack a fundamental feature like projection. In fact, DAX allows you add columns to an existing table, but you cannot simply remove some column from a table. In order to do that, you have to use SUMMARIZE, or you can use ADDCOLUMNS starting from a column that you want to include in the query output and that has to be unique for each row of the result.
Calculating a ratio in DAX is relatively simple in case the underlying data model is a star schema, but you have to consider additional complexities whenever you have a more normalized model, even just a snowflake schema. In this article, you will see how to perform the right computation.
Sorting a column by using another column is one of the nice features of the new version of PowerPivot introduced with SQL Server 2012 (we call it PowerPivot v2, but it really v11 because it is aligned with SQL Server). For example, this allows you to sort the Month name in the right order (January, February, …) instead than in alphabetical order. Usually, you should have a month number column that allows you to correctly sort the month name. However, sometime it is necessary to build a calculated column in order to provide the right sort, as you will see in this article.
We presented a few session at SQLBits and the video of these sessions are available.
This is the list of sessions available from SQLBits 8 and SQLBits 9 – just click the link on the title and jump to the session page with video, slide deck and demo!
A BISM Tabular model can be queried by using both MDX and DAX. These two different languages rely on different modeling concepts, because MDX has a semantic based on dimensions, attributes, hierarchies and measures, whereas DAX only knows table and columns. In order to understand these differences and how to find a corresponding behavior in DAX and assuming you already have MDX knowledge, I’d like to examine how you can convert an MDX query in DAX.
Using Visual Studio to author SSAS Multidimensional projects, I was used to copy the project, deploy it with another name and then proceed with updates, until I was satisfied with the final result. Then, working with the two projects side-by-side I could check performance and numbers.
Having read this question on the mdsn blogs, I investigated on the KEEPFILTERS function and, after having learned it, it is now time to write about it. Moreover, before start to write about it, I need to thank the dev team of SSAS and Marco Russo who helped me understanding this complex topic.
In PowerPivot you can import data from several sources and it is often the case that you import a DateTime column from a database. From an analytical point of view, you usually make analysis over dates (day/month/year/quarter/week) and over time (hour/minute) but to do that it is often useful to have two independent set of attributes: one for the date (the Calendar table) and one for time (for those businesses where this element is relevant).
In an Analysis Services cube you can define drillthrough actions on a cube. This feature has been working since SQL 2005 and now it will be available in both BISM Multidimensional and BISM Tabular in the next version of SQL Server “Denali”.
Year-To-Date and Difference-Over-Previos-Year (or Year-Over-Year Growth) are among the most required features of any user. Some OLAP client (like ProClarity) offers features that try to solve this problem client-side, but I don’t like this approach given that you might have a server-side feature doing that (so you don’t discriminate Excel users).