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.
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.
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
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
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.