Save 10% on Power Pivot Workshop Video Courses

PowerPivot


Alternative use of FIRSTNONBLANK and LASTNONBLANK

NONBLANK 01

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.


Custom Year-Over-Year Calculation in DAX

Custom YOY in DAX - 01

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.


Tools for DAX and Tabular Developers

This page contains descriptions and links to necessary tools and resources for DAX and Tabular developers.


AutoExist and Normalization

AutoExist_F01

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.


Rolling 12 Months Average in DAX

Rolling Average 12 Months 01

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.


Prepare Data for Power View Forecasting in Power BI

PeriodStart

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.


Check Empty Table Condition with DAX

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.


FILTER vs CALCULATETABLE: optimization using cardinality estimation

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.


Natural Hierarchies in Power Pivot and Tabular

UnnaturalHierarchySample

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.


Semi-Additive Measures in DAX

FIG_02_08

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.