Alberto Ferrari wrote an interesting article about a strange behavior of AutoExist in normalized data models. I always say that a star schema is the best practice in Power Pivot and Tabular data modeling. The issue described by Alberto is another good reason to avoid snowflake schemas. I think that an example is better than many words. Consider ...

The adoption of DAX is slowly growing and we can see that from comments to article, blog posts, web site traffic (including DAX Formatter usage). For this reason, the demand for more advanced training about DAX is also increasing. We had great feedback from our last white papers (Using Tabular Models in a Large-scale Commercial Solution and SSAS ...

I recently wrote a short article about filtering data in DAX. In the last month I’ve seen a growing number of people with a SQL background going to Tabular, without having a previous experience of MDX. In my opinion, learning DAX is easier if you try to match its features to SQL rather than to MDX. The reality is that DAX is somewhere in the ...

In one of the Advanced DAX Workshop I taught this year, I had an interesting discussion about how to optimize a SWITCH statement (which could be frequently used checking a slicer, like in the Parameter Table pattern). Let’s start with the problem. What happen when you have such a statement? Sales :=     SWITCH ( ...

Alberto Ferrari wrote an interesting article about DIVIDE performance in DAX. This new function has been introduced in SQL Server Analysis Services 2012 SP1, so it is available also in Excel 2013 (which still doesn’t have other features/fixes introduced by following Cumulative Updates…). The idea that instead of writing: IF ( Sales[Quantity] ...

Using decimal numbers in Power Pivot and Tabular might produce small rounding differences in certain calculations. This is nothing new when you work with floating point, as many programmer knows. The implementation of RANKX might suffer of a behavior producing wrong results when the measures used for the ranking returns a decimal value. For ...

A few days ago I published a new article on DAX Patterns web site describing how to implement Basket Analysis in DAX. This topic is a very classical one and is also covered in the many-to-many revolution white paper. It has been also discussed in several blog posts, listed here in historical order: Simple Basket Analysis in DAX by Chris Webb ...

A common question I receive from Excel users learning Power Pivot is how to handle table that have different granularities. In reality, this terminology is not the one they use: the concept of “table granularity” is used mostly by Kimball practitioners, who immediately identify this scenario in a “two fact tables with different granularities” ...

Microsoft silently added the ISEMPTY function to the DAX language in Analysis Services build 11.00.3368 (SQL Server 2012 SP1 CU4). This function is particularly important in DAXMD (when you use DAX to query a Multidimensional model), because produces a much better execution plan in OLAP than the alternatives based on COUNTROWS. There is an ...

I recently published the ABC Classification article in www.daxpatterns.com, which is a more structured and organized way that recap what I already described in this blog a few years ago (see ABC Analysis in PowerPivot). The pattern describe how to implement the classification through calculated columns, so we consider it a specialization of the ...